Complex SQL Questions for Interview Preparation

Currently Playing: SQL Portfolio Project for Data Analysis | SQL Interview Questions and Answers

In this video we will solve a complete SQL test consist on 7 interview questions. This would be a great exercise to practice SQL. Start your data analytics journey: https://www.namastesql.com/ script: CREATE TABLE users ( USER_ID INT PRIMARY KEY, USER_NAME VARCHAR(20) NOT NULL, USER_STATUS VARCHAR(20) NOT NULL ); CREATE TABLE logins ( USER_ID INT, LOGIN_TIMESTAMP DATETIME NOT NULL, SESSION_ID INT PRIMARY KEY, SESSION_SCORE INT, FOREIGN KEY (USER_ID) REFERENCES USERS(USER_ID) ); -- Users Table INSERT INTO USERS VALUES (1, 'Alice', 'Active'); INSERT INTO USERS VALUES (2, 'Bob', 'Inactive'); INSERT INTO USERS VALUES (3, 'Charlie', 'Active'); INSERT INTO USERS VALUES (4, 'David', 'Active'); INSERT INTO USERS VALUES (5, 'Eve', 'Inactive'); INSERT INTO USERS VALUES (6, 'Frank', 'Active'); INSERT INTO USERS VALUES (7, 'Grace', 'Inactive'); INSERT INTO USERS VALUES (8, 'Heidi', 'Active'); INSERT INTO USERS VALUES (9, 'Ivan', 'Inactive'); INSERT INTO USERS VALUES (10, 'Judy', 'Active'); -- Logins Table INSERT INTO LOGINS VALUES (1, '2023-07-15 09:30:00', 1001, 85); INSERT INTO LOGINS VALUES (2, '2023-07-22 10:00:00', 1002, 90); INSERT INTO LOGINS VALUES (3, '2023-08-10 11:15:00', 1003, 75); INSERT INTO LOGINS VALUES (4, '2023-08-20 14:00:00', 1004, 88); INSERT INTO LOGINS VALUES (5, '2023-09-05 16:45:00', 1005, 82); INSERT INTO LOGINS VALUES (6, '2023-10-12 08:30:00', 1006, 77); INSERT INTO LOGINS VALUES (7, '2023-11-18 09:00:00', 1007, 81); INSERT INTO LOGINS VALUES (8, '2023-12-01 10:30:00', 1008, 84); INSERT INTO LOGINS VALUES (9, '2023-12-15 13:15:00', 1009, 79); -- 2024 Q1 INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (1, '2024-01-10 07:45:00', 1011, 86); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (2, '2024-01-25 09:30:00', 1012, 89); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (3, '2024-02-05 11:00:00', 1013, 78); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (4, '2024-03-01 14:30:00', 1014, 91); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (5, '2024-03-15 16:00:00', 1015, 83); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (6, '2024-04-12 08:00:00', 1016, 80); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (7, '2024-05-18 09:15:00', 1017, 82); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (8, '2024-05-28 10:45:00', 1018, 87); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (9, '2024-06-15 13:30:00', 1019, 76); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (10, '2024-06-25 15:00:00', 1010, 92); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (10, '2024-06-26 15:45:00', 1020, 93); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (10, '2024-06-27 15:00:00', 1021, 92); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (10, '2024-06-28 15:45:00', 1022, 93); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (1, '2024-01-10 07:45:00', 1101, 86); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (3, '2024-01-25 09:30:00', 1102, 89); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (5, '2024-01-15 11:00:00', 1103, 78); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (2, '2023-11-10 07:45:00', 1201, 82); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (4, '2023-11-25 09:30:00', 1202, 84); INSERT INTO LOGINS (USER_ID, LOGIN_TIMESTAMP, SESSION_ID, SESSION_SCORE) VALUES (6, '2023-11-15 11:00:00', 1203, 80); Zero to hero(Advance) SQL Aggregation: https://youtu.be/5Ighj_2PGV0 Most Asked Join Based Interview Question: https://youtu.be/xR87ctOgpAE Solving 4 Trick SQL problems: https://youtu.be/Ck1gQrlS5pQ Data Analyst Spotify Case Study: https://youtu.be/-YdAIMjHZrM Top 10 SQL interview Questions: https://youtu.be/Iv9qBz-cyVA Interview Question based on FULL OUTER JOIN: https://youtu.be/KQfWd6V3IB8 Playlist to master SQL : https://youtube.com/playlist?list=PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb Rank, Dense_Rank and Row_Number: https://youtu.be/xMWEVFC4FOk #sql #dataengineer