SQL Test Based on Real Interview | SQL Interview Questions and Answers

Рет қаралды 19,054

Күн бұрын

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: 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:
• All About SQL Aggregat...
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Trick SQL problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
#sql #dataengineer

Пікірлер: 81

@ankitbansal6 19 күн бұрын

It takes a lot of effort to make these videos. Please do hit the like button and subscribe to the channel.

@macx8360 18 күн бұрын

anything for you majesty

@sushantkumar7450 18 күн бұрын

Ankit I do like and watch your videos regularly, but you also know where the crowd goes and which kind of channels got millions of subscribers.🙂

@ankitbansal6 18 күн бұрын

@@sushantkumar7450 please eloborate 😊

@vijaypradeep8315 18 күн бұрын

@@ankitbansal6 ankit can you make an class on complete de i will for sure subscribe it

@sushantkumar7450 17 күн бұрын

@@ankitbansal6 Hi Ankit, I was not commenting on the quality of your videos. for me, your playlist is most valuable SQL vedios I have ever gone through in youtube. I just wrote that comment on a lighter note regarding people watching mostly useless family vlogs and some other channels and those creators get subscribes in millions. It is a general trend in all types of youtube channels let it be politics, spiritual, sports.😊

@LearnSQL2024 17 күн бұрын

Hi Ankit, You're really awesome, helping so many. Last Question in PostgreSQL: with cte as( select generate_series(min(login_timestamp),max(login_timestamp),'1 day')::date as login_timestamp from logins) select login_timestamp from cte where login_timestamp not in (select distinct login_timestamp::date from logins)

@insight.miner08 18 күн бұрын

Thanks Ankit for providing these interview questions

@philosaifer96 18 күн бұрын

Really appreciate your efforts.. always happy to learn from you..

@abhijeetsonwani5950 18 күн бұрын

Your Lecture is awesome sir. 🙏🙏

@TwoMinutesisEnough 13 күн бұрын

You are mind-blowing

@sidindian1982 13 күн бұрын

Brilliantly Explained sir .... Mindblowing ... 🙂

@keshavkaranam 6 күн бұрын

I wish more folks watch your channel before giving interviews , solid stuff. Infact , they should pay you 10% post getting a new job ;)

@aman_mashetty5185 18 күн бұрын

one of the best video and as always nice explanation Ankit

@newmanokereafor2368 18 күн бұрын

Thank you @Ankit. Please share the questions as you did the CREATE & INSERT statements. Thank you once again.

@GiriPrasath.D 18 күн бұрын

Learned many new date functions, from this video, do it more

@rakeshpanigrahi577 13 күн бұрын

AWSM Sir!!

@user-vy2xj8iu7h 8 күн бұрын

Good one !!

@sudhirsharma9103 3 күн бұрын

Thank you Ankit, it's really interesting and cool stuff

@Datapassenger_prashant 18 күн бұрын

Hi Ankit Sir, Thank you for this wonderful Case Study.. It took 70 mins for me to solve all the questions.. only for 6th question I used a bit complex query when compared to yours. other wise everthing were good. enjoyed a lot solving these problem statement.. and for last one I used recursive cte..

@ankitbansal6 18 күн бұрын

Well done

@pspreetesh4166 10 күн бұрын

very good session sir

@MazharKhan-hx7xu 12 күн бұрын

Hi Ankit, I appreciate your learning session. Could you please make some videos apart from SQL queries like SQL Dashboard operation, basic options, error handling part, trigger, procedure etc...

@Gareeb_ Күн бұрын

❤❤❤❤❤❤❤❤ i don't have any words 🎉🎉🎉🎉🎉🎉 Love you 🤟 bro

@NiranjanDevaraddi 16 күн бұрын

Hi Ankith, It's one of the best SQL videos, and you have amazing explanation skill.

@ankitbansal6 15 күн бұрын

Glad you liked it!

@varunas9784 18 күн бұрын

Just the video I needed to revise! Thanks a ton Ankit!! Just a twist to your 2nd Q, solve the same question (#2) WITHOUT using DATETRUNC function it's quite interesting! Anyone reading this comment, comment your approach in above case, its fun!

@kadagaladurgesh3691 17 күн бұрын

In mysql : select count(distinct USER_ID) user_count, count(SESSION_ID) as session_count, min(LOGIN_TIMESTAMP) as min_date, date_sub(min(LOGIN_TIMESTAMP), interval dayofmonth(min(LOGIN_TIMESTAMP)) -1 day) as first_day from LOGINS group by QUARTER(LOGIN_TIMESTAMP) could you share any other approach I am happy to learn if there is any better or optimized approach

@exanode 17 күн бұрын

-- 2. Quarter Analysis [Oracle SQL] SELECT TO_CHAR(ADD_MONTHS(TO_DATE('01/01/2024', 'MM/DD/YYYY'), 3 * (TO_CHAR(LOGIN_TIMESTAMP, 'Q') - 1)), 'MM/DD') || '/' || TO_CHAR(MIN(LOGIN_TIMESTAMP), 'YYYY') AS quarter, COUNT(*) AS sessions, COUNT(DISTINCT user_id) AS unique_users FROM logins GROUP BY TO_CHAR(LOGIN_TIMESTAMP, 'Q') ORDER BY 1;

@ambeshpandey8937 18 күн бұрын

Superb video Ankit

@ankitbansal6 18 күн бұрын

Thank you so much 😀

@amberjain7355 Күн бұрын

Hi Ankit, it is really helpful, could you please start creating content on AWS and pyspark. Also just to add on in 1st question we need to use user tables and there might be the case user has never logged in so that entry might not come in logins table??

@thegamingtron8698 18 күн бұрын

Thanks Ankit! Not correcting you at all, but the 2nd question condition is to sort from newest to oldest. Result should be order by datetrunc(quarter,login_timestamp) desc "Nevertheless, exceptional learning as always."

@ankitbansal6 18 күн бұрын

I think I missed sorting it 😊 thanks for pointing out 🙏

@UjjwalSinghPal-um3pb 15 күн бұрын

if i started learning for now than there are very less data engineering internships(if one has data internship it is easy to get a job ,and that person also get hand on experience ) ,,,,so should i do data analyst first only for internship and after that i will study for data engineering for job because it is also a good way , and i need to do an internship because it is in our college criteria , they do not support much if you do not have done internship?? informative video by the way

@shawnsmothers1715 13 күн бұрын

I think this i great but i do think its more advance than most will ask. I cant see many companies asking these questions to juniors. One because they are pretty complex and two because of time.

@rajvihari4326 9 күн бұрын

If possible, can we use min(user_id), and convert it in simple query: *Twist is, it will give min(user_id) but max(score). select cast(LOGIN_TIMESTAMP as date), max(SESSION_SCORE) max_score, min(user_id) user_id from logins group by cast(LOGIN_TIMESTAMP as date)

@JackHugh-eh4hl 18 күн бұрын

Great video Ankit... nothing major, I think in question 5 it should be max(score) not sum

@ankitbansal6 18 күн бұрын

We need to do sum at user and day level first then find the max for each day using row number

@jayashreepaul3890 13 күн бұрын

In the first question There are two tables we can join the two tables and implement the query right? With cte (Select * from logins join users on users.userid= logins.user_id )as l Select user_id,login_timestamp From l As follows in the video..........

@dgsharma4773 17 күн бұрын

Hello ankit, In question number 5 you missed user_name

@houseofroses3942 17 күн бұрын

I want to learn very complex problems i solved all the questions without any problem as i work on sql daily but i want to improve my problem solving. Kindly guide sir

@ankitbansal6 16 күн бұрын

Check this Complex SQL Questions for Interview Preparation: kzfaq.info/sun/PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb

@shivammishra-mk9jp 15 күн бұрын

Query for 1 ques. not working in mysql. I used date_add funtion compatible with mySQL but it is throwing an error. select user_id , max(LOGIN_TIMESTAMP) from logins group by user_id having max(LOGIN_TIMESTAMP) < date_add(LOGIN_TIMESTAMP,INTERVAL -5 MONTH) Error:- Unknown column in having clause.....

@PowerBiLearning-xh8wk 14 күн бұрын

i was getting this error with datetrunc function 'DATETRUNC' is not a recognized built-in function name.'

@prabhatthakur4257 18 күн бұрын

These questions asked for a experienced role or 1-2 year experience?

@ankitbansal6 18 күн бұрын

3+ years of experience

@abdulashwaq7312 14 күн бұрын

I have query base city like, i need to check piiza send across city like bangalore, mumbai , delhi etc, pls help how write this code using slq table called pizza.

@ankitbansal6 14 күн бұрын

Share data and expected output

@rohitsharma-mg7hd 16 күн бұрын

for second question : if there is no login in april month (2024-04) then in ur case minimun date will come as (2024-05), then this is not first day of quarter. so your answer might fail in that case , for accuracy below case statement can be used which wont fail in above case : case when qtr=1 then concat(yr,'-01-01') when qtr=3 then concat(yr,'-06-01') when qtr=2 then concat(yr,'-03-01') when qtr=4 then concat(yr,'-09-01') end as day_qtr

@ankitbansal6 16 күн бұрын

It will work as we are doing the date trunc of quarter not month

@rohitsharma-mg7hd 16 күн бұрын

@@ankitbansal6 yes u r right

@mohdtoufique7446 17 күн бұрын

Hi Ankit! Thanks for the content My approach from q4 onwards 4)WITH quarter_cnt AS( select YEAR(cast(login_timestamp AS Date)) AS login_year,DATEPART(QUARTER,cast(login_timestamp AS Date)) AS login_quarter, COUNT(DISTINCT USER_ID) AS user_cnt, COUNT(DISTINCT SESSION_ID) AS session_cnt from logins GROUP BY YEAR(cast(login_timestamp AS Date)),DATEPART(QUARTER,cast(login_timestamp AS Date))) SELECT CASE WHEN login_quarter=1 THEN CONCAT('01-01-',login_year) WHEN login_quarter=2 THEN CONCAT('01-04-',login_year) WHEN login_quarter=3 THEN CONCAT('01-07-',login_year) WHEN login_quarter=4 THEN CONCAT('01-10-',login_year) END AS qrtr_str_date,session_cnt, (session_cnt-(lag(session_cnt)OVER(ORDER BY login_year,login_quarter)))*100.0/lag(session_cnt)OVER(ORDER BY login_year,login_quarter), user_cnt FROM quarter_cnt --5) WITH score_day AS( SELECT cast(login_timestamp AS Date) AS login_day,user_id, sum(session_score) AS total_score FROM logins GROUP BY cast(login_timestamp AS Date),user_id), rn_cte AS( SELECT *,DENSE_RANK()over(PARTITION BY login_day ORDER BY total_score DESC) AS rn FROM score_day) SELECT * FROM rn_cte WHERE rn=1 ----6) WITH user_login_history AS( SELECT USER_ID, min(cast(LOGIN_TIMESTAMP AS DATE)) as FIRST_LOGIN, MAX(CAST('2024-06-28' AS Date)) AS Last_Date, COUNT(distinct cast(LOGIN_TIMESTAMP AS DATE)) as no_logins FROM logins GROUP BY USER_ID) SELECT *,datediff(DAY,FIRST_LOGIN,Last_Date)+1 AS logins_btw_str_end FROM user_login_history WHERE (datediff(DAY,FIRST_LOGIN,Last_Date)+1)=no_logins ---7) WITH master_dates as( select cast('2023-07-15' AS Date) AS login_date UNION all SELECT DATEADD(day,1,login_date) FROM master_dates WHERE login_date

@ritudahiya6223 15 күн бұрын

@Ankit Bansal Can you please write the answer for this query.it will take 2 mins A. B 1. 2 2. 2 1. 3 No value 4 Null. Null There are 2 tables .table A have one row where we have blank value(row 4) .please give output for innere join and left join Eagerly waiting for your reply

@ritudahiya6223 14 күн бұрын

@ankitbansal please reply on this..I have an interview tomorrow...

@ritudahiya6223 14 күн бұрын

@ankitbansal I just want to know what will be the output when there is blank row in one table

@rajathratnakaran7893 10 күн бұрын

@@ritudahiya6223 Blank space will be considered as a another character, it is not same as null. Two nulls aren't equal, blanks are same as 1=1,2=2. Thanks for this question. I created a table and figured out myself. Adding details below, so that you can also try. CREATE TABLE T (name char(20)); insert into t values ('a'), ('b'), ('c'), ('a'), ('a'), (''), ('b'), (null); select * from T; CREATE TABLE T1 (ch char(20)); insert into t1 values ('b'), ('a'), (''), ('c'), ('a'), (''), ('d'), (null); left join : 12 rows right join : 13 rows inner join : 11 rows

@shubhamanand2661 10 күн бұрын

Inner - 2 Left - 6 Right - 5

@ritudahiya6223 9 күн бұрын

Yes inner join will be 2 and not 11 rows..anyways thanks for replying

@dasoumya 18 күн бұрын

Why DATETRUNC is not working in my SQL server 😢 it says 'datetrunc' is not a recognized built-in function name.

@ankitbansal6 18 күн бұрын

Maybe older version

@varunas9784 17 күн бұрын

@@dasoumya I guess DATETRUNC was introduced as a party of SQL server 2022. You might wanna go through Microsoft's documentation to see if your version of SQL server supports this or not

@ananddalmia4044 17 күн бұрын

Good morning sir, Please help me for setup the sql environment, i show lot of video but some time network adopter error is showing Please suggest me where should i pratice sql or which platform is good for pratice 🙏🙏🙏🙏🙏 i installed oracle 19c.....

@ankitbansal6 17 күн бұрын

Install SQL server or MySQL

@ananddalmia4044 17 күн бұрын

@@ankitbansal6 sir please suggest me any video because I am facing issue while installing it was showing monthly charges like that 🙏🙏🙏🙏

@ananddalmia4044 17 күн бұрын

@@ankitbansal6 sir could you provide me link I am using windows and one more thing sir please provide me guidelines sir for data analytics if u help me out it will be helpful for me 🙏🙏

@SK-wp4tm 18 күн бұрын

I am waiting for India to win then I will get 50% discount 😋

@ankitbansal6 18 күн бұрын

Use code INDIA

@karthikvijay5626 17 күн бұрын

Hi,Ankit iam preparing SQL interview questions and Practice Questions with answers but i want in MYSQL if you make upcoming vedios in MYSQL workbench it will be helpful for me its my kind request ..

@exanode 18 күн бұрын

Perfect timing! I have an interview in two days. I've solved these in Oracle. -- 1. Usernames that did not login in the past 5 months SELECT user_id, user_name FROM users WHERE user_id NOT IN ( SELECT user_id FROM logins WHERE login_timestamp > ADD_MONTHS(SYSDATE, -5) ); -- 2. Quarter Analysis SELECT TO_CHAR(ADD_MONTHS(TO_DATE('01/01/2024', 'MM/DD/YYYY'), 3 * (TO_CHAR(LOGIN_TIMESTAMP, 'Q') - 1)), 'MM/DD') || '/' || TO_CHAR(MIN(LOGIN_TIMESTAMP), 'YYYY') AS quarter, COUNT(*) AS sessions, COUNT(DISTINCT user_id) AS unique_users FROM logins GROUP BY TO_CHAR(LOGIN_TIMESTAMP, 'Q') ORDER BY 1; -- 3. Users in Jan2024 but not in Nov2023 SELECT DISTINCT user_id FROM logins WHERE TO_CHAR(LOGIN_TIMESTAMP, 'MM/YYYY') = '01/2024' AND user_id NOT IN ( SELECT user_id FROM logins WHERE TO_CHAR(LOGIN_TIMESTAMP, 'MM/YYYY') = '11/2023' ); -- 4. Percent increase from ans2 WITH raw_data AS ( SELECT TO_DATE( TO_CHAR( ADD_MONTHS(TO_DATE('01/01/2024', 'MM/DD/YYYY'), 3 * (TO_CHAR(LOGIN_TIMESTAMP, 'Q') - 1)), 'MM/DD') || '/' || TO_CHAR(MIN(LOGIN_TIMESTAMP), 'YYYY'), 'MM/DD/YYYY') AS quarter, COUNT(*) AS sessions, COUNT(DISTINCT user_id) AS unique_users FROM logins GROUP BY TO_CHAR(LOGIN_TIMESTAMP, 'Q') ORDER BY quarter ) SELECT QUARTER, SESSIONS, unique_users, ROUND((SESSIONS / LAG(SESSIONS, 1, SESSIONS) OVER (ORDER BY quarter)) * 100 - 100) AS pct FROM raw_data; -- 5. Users with highest scores [will show multiple entries is more than one max] WITH cte AS ( SELECT user_id, TRUNC(login_timestamp) AS login_time, SUM(session_score) AS total_score FROM logins GROUP BY user_id, TRUNC(login_timestamp) ) SELECT user_id, login_time, MAX(total_score) AS score FROM cte GROUP BY user_id, login_time ORDER BY user_id, login_time; -- 6. Users logged in everyday WITH cte AS ( SELECT user_id, MAX(trunc(login_timestamp)) OVER (PARTITION BY user_id) AS max_date, MIN(trunc(login_timestamp)) OVER (PARTITION BY user_id) AS min_date, COUNT(DISTINCT trunc(login_timestamp)) OVER (PARTITION BY user_id) AS distinct_dates_count FROM logins ) SELECT DISTINCT user_id FROM cte WHERE max_date - min_date = distinct_dates_count - 1; -- 7. Dates without login WITH cte (start_date) AS ( SELECT MIN(TRUNC(login_timestamp)) AS start_date FROM logins UNION ALL SELECT start_date + 1 FROM cte WHERE start_date < (SELECT MAX(TRUNC(login_timestamp)) FROM logins) ) SELECT * FROM cte WHERE start_date NOT IN ( SELECT DISTINCT TRUNC(login_timestamp) FROM logins ) ORDER BY 1;

@ankitbansal6 18 күн бұрын

Great work 👏