Episode 1 : Crazy SQL Interview | iPhone 15 Fever
ฝัง
- เผยแพร่เมื่อ 3 ต.ค. 2023
- We are starting a new interview series and this is the first video in the series. This mock SQL interview will test some advanced concepts of SQL.
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
3 days to go for my SQL for Analytics ( zero to hero) live weekend bootcamp 👢⛺
www.namastesql.com/courses/Think-SQL-Go-from-Zero-to-Hero-October-64f59039e4b00ea856fa045d
Here is what you will get :
1- 12+ Live sessions (2 hours each)
2- 2 portfolio projects with solution
3- Classes will be from absolute basic to covering all the advanced concepts including sub queries, cte , window functions , indexes etc..
4- Assignments after each session.
5- FAANG interview questions for practice.
6- True Bonus worth Rs 5000 ( Access to premium subscription to a SQL practice website)
7- A full session on Tableau fundamentals.
8- Access to the premium community of data professionals.
And much more
There are limited seats.
Use code : EARLY24
#sql #analytics #weekend
Do u recommend taking notes while learning thru namastesql ?
@@vishalmane3139 not required. Notes files are available as attachment
@@ankitbansal6for the 1st question. Can we write the below
Select * from user where iPhone_model like '%12' and like '%15'
This is SO SO WONDERFUL
Please come up with more such videos
I hope for first question we can use like this
Select user_id from phones_table where user_id not in ( Select user_id from phones_table where iphone_model = 'i-11');
with cte as (select *, count(user_id) over (partition by user_id) count_of_user_id
from iphone)
select * from
cte
where count_of_user_id=1 and iphone_model='i-15';
It worked for me.
00:04 Query to analyze the number of users who have bought iPhone 15 only
02:53 Find users who have bought iPhone 15 and their count
08:55 Filter iPhone models equal to 1
11:28 Using the having clause in SQL for filtered aggregated values
17:28 Total number of users buying iPhone 15 for the first time is 1, and total number of users upgrading to iPhone 15 is 2.
19:53 There are two conditions for upgradation: user ready and another city
25:02 Simplified query writing and removing unnecessary conditions
27:55 Query to find users who have bought all models of iPhone
33:01 Retrieve user IDs and their respective counts.
35:17 Focus on your daily work activities
Please bring more such interview content
Thank you so much for this series. Waiting for next one
More to come!
You are amazing 🤩
4th Query
select user_id
from phones
group by user_id
having count(distinct phones. iphone_model)= (select count( distinct product_dim) from products);
For third problem when user shift from any model to i-15 we can solve it easily by window function and self join
- For total user who upgraded to i-15 or bought only i-15
with cte as (
select user_id,count(model) as model_count
from iphone
group by user_id)
--having count(user_id) > 1
select
sum(case when c.model_count = 1 and i.model = 'i-15' then 1 else 0 end) as new_user,
sum(case when c.model_count >1 and i.model in ('i-15') then 1 else 0 end) as upgrader_user
from cte c
inner join iphone i on c.user_id = i.user_id
Bhai simple si baat hai….
1st query : SELECT user , COUNT (phonemodel) AS cnt FROM PhoneTable GROUP BY user HAVING cnt < 2;
Ankit thank you for uploading such an interesting interview.
For 3 query can we use this approach :
with cte as(
select *
count(model) over(partition by userid order by model) as cnt
from table
)
select user id,
sum(case when cnt=1 then 1 else 0 )end as first_time,
sum(case when cnt=2 then 1 else 0 )end as upgrading
from cte
With Cte1 as (select user_id from orders where product = iphone15),
cte2 as ( select user_id from orders where product != iphone15),
Cte3 as (select user_id from products where product = iphone12)
Select “iphone15” as product, “first_time” as frequency, user_id from cte1 where user_id not in (select user_id from cte2)
Union all
select “iphone15” as product, “upgraded from 12” as frequency,user_id from cte1 where user_id in (select user_id from cte3)
Looks like my query needs optimisation but I can say this looks easy 😅
With Cte as
Select row number() ( over partition by iPhone model ) as no_of_model from phone table
Select User_id , count (no_of_model)
From Cte
having count(no_of_model )= 1 and iPhone model = 15
Group by used_id
4th one
select user_id
from phones
group by user_id having count(*) =
(select count(*) from phone_dim)
right right
with cte as ( select id, case when model = 'i15' then 0 else 1 end as model_flag from model)
select id from cte group by id having sum(model_flag) = 0
2nd Solution:
with cte as (select *, lag(iphone_model) over (partition by user_id order by iphone_model ) prev_value
from phones)
select * from cte
where iphone_model='i-15' and prev_value='i-12';
with cte as (
select count(user_id) as for_first_time
from phones
where user_id in (select user_id
from phones where iphone_model = 'i-15') and user_id not in (select user_id
from phones where iphone_model != 'i-15') )
, cte1 as (
select count(*) as upgrade_one from (
select user_id
from phones
where user_id in (select user_id from phones where iphone_model = 'i-15' )
group by user_id having count(distinct iphone_model) > 1 ) as s )
select *
from cte , cte1 3rd one
mantos for 3rd one
select sum(case when iphone_model = 'i-15' and r =1
then 1 else 0 end) as first_time , sum(case when iphone_model
= 'i-15' and r !=1
then 1 else 0 end) as upgrade_one from (
select * , row_number() over(partition by user_id order by iphone_model) as r
from phones ) as k
I thought of using LAG() function in second query.
with t1 as (
select *,
count(userid) over (partition by userid) as total_purchases
from iphone
)
select count(*) no_of_users from t1
where total_purchases=1 and Model='i-15'
For question 2, won't a self join be simpler? Join table1 and table2 of the same table based on the equality of the user id and using a where clause for table1.iphonemodel=i12 and table2.iphonemodel=i15? Then obviously we can group by and count this up (we can use distinct just in case some rich kids don't buy 2 iphone12s or 2 iPhone 15s)
For first one we can use except operator right
answer 1 :
with cte as (
select * from iphone where user_id in(
select user_id from iphone group by user_id having count(distinct phone)=1))
select * from cte where phone = "i15"
finished watching
I dont know whether it is a right approach or not just tried the below approach
Create table test_test(
id int,
model string
)
id, model
1,i-11
1,i-15
1,i-14
2,i-15
3,i-15
4,i-12
4,i-15
select id, model from test_test where id not in (
select id from (select id, model, lag(model) over (partition by id order by model ) previous_model from test_test order by id, model) where previous_model is not null )
using subqueries is not the best approach for performance. Kindly correct me if my approach is wrong
Thanks
select user_id from phones group by user_id having count(iphone_model) =1 and iphone_model = i-15; is it correct?
the first question could done very easily by just aggregating min iphone model on user level and using having by clause to filter 1phone 15 as min model
in the last question if there is a user with ID 4 and he has 5 i-11 phones then that will be selected r8... then how we can identify that person having all phones models. As per the question we want to know how many are there who have all the models of the phones.
first we need to find out number of purchases of every user and then check if all purchases were iphone 15 or not
select user from iphone p1
group by user_id
having count(user_id) = (
select count(*) from iphone
where user = p1.user_id and model="i-15")
in question 2 if we equate the count of models and the sum of i phone 12 and 15 it will give user_id 2 and 3 which is not our answer because in having if we count models for 2 user it will be 1 and sum of i-12 and i-15 will be 1
Hi Ankit, Please correct me if I am mistaken but In the last query where you are finding total count for each user, i think you should do count(distinct iphone_model) instead of count( iphone_model) since there could be a user who has purchased any specific model twice and will get over counted if we just do a count. Pease let me know if i am missing something.
Query acc to me:
select user_id, count(distinct iphone_model) as Phones_CNT
from phones_table
group by user_id
having count(distinct iphone_model) = ( select count(product_no) from product_dim)
this thing also comes to my mind when saw the video @ankitbansal6
select * from #phone where user_i in ( select user_i from #phone group by user_i having count(*) = 1 ) and phone_model = 'i15'. Happy coding.
Sir, in question 2 using your query it will qualify user_id 2 and 3 both .
Because in having clause
"count(iphone_model) = sum(case when iphone_model = 'i-12' then 1 when iphone_model = 'i-15' then 1 else 0 end)"
For user_id 2 : count(iphone_model) = 1 and sum(case when iphone_model = 'i-12' then 1 when iphone_model = 'i-15' then 1 else 0 end) = 1
For user_id 3 : count(iphone_model) = 2 and sum(case when iphone_model = 'i-12' then 1 when iphone_model = 'i-15' then 1 else 0 end) = 2 .
I am writing my query below please correct where I'm doing mistake :
CREATE TABLE iPhoneData (
user_id INT,
iphone_model VARCHAR(20)
);
-- Inserting data
INSERT INTO iPhoneData (user_id, iphone_model) VALUES (1, 'i-11');
INSERT INTO iPhoneData (user_id, iphone_model) VALUES (1, 'i-15');
INSERT INTO iPhoneData (user_id, iphone_model) VALUES (2, 'i-15');
INSERT INTO iPhoneData (user_id, iphone_model) VALUES (3, 'i-12');
INSERT INTO iPhoneData (user_id, iphone_model) VALUES (3, 'i-15');
select user_id,sum(case when iphone_model = 'i-12' then 1 when iphone_model = 'i-15' then 1 else 0 end),count(iphone_model)
from iphoneData
group by user_id
having count(iphone_model) = sum(case when iphone_model = 'i-12' then 1 when iphone_model = 'i-15' then 1 else 0 end)
I came up with this solution:
-- who has bought i-15 only
select user_id
from iphone
group by user_id
having min(iphone_model) = 'i-15';
-- user who upgraded iphone 12 to iphone 15
select a.user_id
from iphone a
join iphone b on a.user_id = b.user_id
and a.iphone_model < b.iphone_model
a.iphone_model = 'i-12'
and b.iphone_model ='i-15';
-- total users i-15 for first time & total users who are upgrading from any phone
-- display in 2 cols: output will be 1 and 2
with cte as
(select count(user_id) as col1
from iphone
group by user_id
having min(iphone_model) = 'i-15'),
cte2 as
(select count(distinct a.user_id ) as col2
from iphone a
join iphone b on a.user_id = b.user_id
and a.iphone_model != b.iphone_model)
select * from
cte, cte2;
-- users whol has bought all the models of iphone
select user_id, count(iphone_model) as user_phone_count
from phones
group by user_id
having user_phone_count = (select count(iphone_model) phone_model_count from product_dim);
First_value(iPhone model) over ( partition by user_id) = 'i-15'
select user_id
from phones
where iphone_model in ('i-12' , 'i-15' )
group by user_id having count(distinct iphone_model ) = 2 , 2nd one if he can buy any number of phones
else
select distinct user_id from phones where user_id in (
select user_id
from phones
where iphone_model in ('i-12' , 'i-15' )
group by user_id having count(distinct iphone_model ) = 2 )
and user_id not in
(select user_id
from phones
where iphone_model not in ('i-12' , 'i-15' )
)
iphone-15 / Q1
-- Create iphone_data table
CREATE TABLE iphone_data (
user_id INT,
iphone INT
);
-- Insert data into iphone_data table
INSERT INTO iphone_data (user_id, iphone) VALUES
(1, 11),
(1, 15),
(2, 15),
(3, 15),
(3, 15),
(4, 11);
SELECT * FROM
iphone_data;
SELECT user_id ,iphone
FROM iphone_data
GROUP BY user_id
HAVING count(DISTINCT iphone) = 1
AND iphone='15';
is it not the SUM () instead of count () in CASE statement ? correct me if I am wrong since whether it's 0 or 1 , count of 0,1 is still 2
Right 👍
SELECT ID FROM AK1 -- AK1 is my data table
WHERE ID NOT IN (SELECT DISTINCT id FROM AK1 WHERE item ! = 'i-15') AND item = 'i-15'
Ankit's 1st question ,expected output is User_ID 2 only. But with second query i am getting both user_id 1 and 2 also.pls Ref.query once.
Select user_id
from Iphone_TBL
group by user_id having count(iphone_model)=count(case when iphone_model='I-15' then 1 Else 0 End);
Instead count use max
Select user_id
from Iphone_TBL
group by user_id having count(iphone_model)=max(case when iphone_model='I-15' then 1 Else 0 End);
SELECT user_id FROM phone_table
where user_id not in (Select user_id from phone_table where phone_model'i-15');
for question 1 -with cte as
(select user_id,iphone,row_number()over(partition by user_id order by user_id) as count from phone )
select max(user_id) from cte....is it correct?
No
I think the first answer is wrong if you are putting condition flag_cnt = total_cnt . In case user have purchased iphone 11 or any other iphone only once those users will also come with the query.
--user who bought only ip15
select userid
from phone
where iphone_model ='ip-15' and userid not in (select userid
from phone
where iphone_model'ip-15' )
--user who upgraded from ip12 to ip15
select userid from (
select * , lead(iphone_model,1) over(partition by userid order by userid) as next_purchase
from phone ) a
where iphone_model='ip-12' and next_purchase='ip-15'
--question 3
with cte1 as
(select count(userid) as customer_only_ip15
from phone
where iphone_model ='ip-15' and userid not in (select userid
from phone
where iphone_model'ip-15' )) ,
cte2 as (select count(userid) as customer_upgrade_to_ip15
from (
select * , lead(iphone_model,1) over(partition by userid order by userid) as next_purchase
from phone ) a
where iphone_model'ip-15' and next_purchase='ip-15')
select customer_only_ip15 , (select customer_upgrade_to_ip15 from cte2) as to_ip15
from cte1
Ankit bro in first quarry
It shouldn't be null instead of 0
-count(case when iphone_model='I-15' then 1 else null end)
You are right. Either we should change the count to sum or make it null as you suggested.
Spot On 👍🏼
Even I was searching for the comment where someone mentions about SUM but even NULL would do the job.
Ok , on 18:30 sec it is corrected .
yes @@ankitbansal6 but why 0 is giving error
Sir bring more episodes of mock interviews please.
Sure
In second query, user id 2 will also qualify. Correct me if i am wrong.
Count(case when iPhone = 15 then 1 else 0 end)
This will give 2 for userid 1 as it will just count rows
....
We have to use
Sum(case when iPhone = 15 then 1 else 0 end)
Will count and sum make difference?
I think it will make difference
Please help here someone!!
for the last question i think th emost easiest way to solve the the problem would be like this:
my approach: count distinct iphone model so that if some peeps r more offsesed with the having two same model and if the count is equal to the count of the product dim then we have those users who have all iphone models.
Code snippet:
with total as (select count(1) from product) - - asuming the product dim column is primary key
select user_id from user,total group by user_id having count(distinct iphone_model)=total.count
Ankit, in the second query user 2 will also qualify. Kindly could you have a look into it.
Yes
First Question: WITH first_iphone AS
(
SELECT user_id,
COUNT(Iphone_model) AS total_count,
COUNT(CASE WHEN Iphone_model = 'IPhone-15' THEN 1 END) AS i_15
FROM phones
GROUP BY user_id
)
SELECT user_id
FROM first_iphone
WHERE total_count = i_15
ORDER BY user_id;
Second Question: WITH phone_cte AS
(
SELECT *,
LAG(Iphone_model,1) OVER(PARTITION BY user_id ORDER BY user_id) AS prev_model
FROM phones
)
SELECT user_id
FROM phone_cte
WHERE Iphone_model = 'IPhone-15'
AND prev_model = 'IPhone-12';
Third Question:
WITH phone_cte AS
( SELECT user_id,
COUNT(Iphone_model) AS total_cnt,
COUNT(CASE WHEN Iphone_model = 'IPhone-15' THEN 1 END) AS iphone_15_cnt
FROM phones
GROUP BY user_id
)
SELECT
SUM(CASE WHEN total_cnt = iphone_15_cnt THEN 1 ELSE 0 END) AS new_customer,
SUM(CASE WHEN total_cnt iphone_15_cnt THEN 1 ELSE 0 END) AS old_user
FROM phone_cte;
select user_id
from phones
where user_id in (select user_id
from phones where iphone_model = 'i-15') and user_id not in (select user_id
from phones where iphone_model != 'i-15')
Every time he said Nachiket It felt like he was talking to me. 😹
Crazy ho gaya Bhai yeh toh
For the first question query :-
with cte as
(select user_id, count(1) from user
group by user_id
having count(1) = 1)
select c.user_id, u.Iphone_model from cte c
join user u on c.user_id = u.user_id
where u.Iphone_model = 'i-15'
a little complicated IMO. And what if a user has bought 2 iPhone 15s then it would appear he's bought iPhone previous. We can use 'COUNT DISTINCT iphone_model' to avoid that problem
2nd question solution is wrong
What if customer only buys one phone which can be 12 or 15 model
In these cases the query written will give the wrong answer
Hi, Ankit! Can I pleaaaase take part in this next time? I'm a fresher. I want a REALITY CHECK of where i stand.
Sure . Send me dm on LinkedIn
I think for first question simply we can use this:-
1. select user_id from mobile
group by user_Id having count(*)=1 and iphone_model='iphone-15'
Que2 - select user_id from
mobile group by user_id
having count(distinct iphone_model)=2 and iphone_model in('iphone-12','iphone15');
Que3- with cte as(
select user_id,lag(iphone_model) over(partition by user_id order by iphone_model) as
prev_model,iphone_model as current_model
from mobile
)
select sum(case when prev_model is Null and current_model='iphone-15' then 1 else 0 end) as new_user,sum(case when prev_model is not Null and current_model="iphone-15" then 1 else 0 end) as old_user
from cte;
Que4: - select user_id from mobile
group by user_id
having count(distinct iphone_model)=(select count(distinct iphone_model) from mob_dim);
in having clause we should use aggregate functions right. in your solution you are directly checking the column value
q2 having should be shifted to where clause
Th first query will fail if someone will buy i11 after i15
1 i11
1 i15
2 i15
2 i11
like for user id 2
it will not give us this user name but this user should come
Answer of the first question is ❌❌❌❌❌wrong. It should be instead of count to get the right answer.
select USER_ID
from i_phone
group by user_id
having COUNT(model)=sum(case when model='i-15' then 1 else 0 end);
Before watching the video:-
Ankit ko bolna parega mera bhi ek mock interview loo..
After complete watching the video:-
Abhi Ankit ko muh nhi dikha sakte
Underground ho jaao 😂😂😅😅
Haha 😆 way to go
hello @ankitbansal sir
i have written this query for find which user brought their first iphone :-
select id from test group by id having count(id)
create table iphone (usr id int, model int);
insert into iphone values (1,11);
insert into iphone values (1,11);
insert into iphone values (2,15);
insert into iphone values (3,11);
Insert into iphone values (3,15);
insert into iphone values (3,15);
Insert into iphone values (4,15);
select * from iphone
with cte as (Select usr id, model, ROW_NUMBER() over (PARTITION by usr_id order by model) as rn from iphone) Select * from cte
where model= 15 AND rn=1
Output
usr_id | model | rn
----------------------------------
2 15 1
4 15 1
i guess in the second question it will give user 2 and user 3 we need only user 3
Q1.
-----
with cte1 as (
select user_id , count(1) as iphone_count from data-engineering-proj.data_analytics.iphone
group by user_id)
select i.* from data-engineering-proj.data_analytics.iphone i inner join cte1 c on i.user_id = c.user_id and c.iphone_count =1
and i.iphone_model='i-15'
Q2
---
select user_id from (
select *
--, count(1) over(partition by user_id order by iphone_model asc) as i_count
, lead(iphone_model,1) over(partition by user_id order by iphone_model asc) as next_1
, lead(iphone_model,2) over(partition by user_id order by iphone_model asc) as next_2
from data-engineering-proj.data_analytics.iphone) A where iphone_model = 'i-12' and next_1='i-15' and next_2 is null
or
select
user_id
from data-engineering-proj.data_analytics.iphone
group by user_id
having count(iphone_model) = sum(case when iphone_model = 'i-12' then 1 when iphone_model = 'i-15' then 1 else 0 end)
--------------------------------------------
Q3
---
with cte1 as (
select user_id
from data-engineering-proj.data_analytics.iphone
where iphone_model = 'i-15')
select i.user_id from data-engineering-proj.data_analytics.iphone i inner join cte1 c on i.user_id=c.user_id
group by i.user_id
having count(1) > 1
Q4
----
select distinct user_id
from (
with cte1 as (
select
models,
count(distinct models) over() as total_models
from data-engineering-proj.data_analytics.product_dim
), cte2 as (
select
user_id
,iphone_model
,count(distinct iphone_model) over(partition by user_id) as user_iphone
from data-engineering-proj.data_analytics.iphone
)
select * from cte1 c1 inner join cte2 c2 on c1.models = c2.iphone_model) iphone_cte where total_models = user_iphone
original table name is phones_table
1. Only the 1st time Iphone15 buyers , who have not bought any other phone before
with non_i15 as
(Select distinct user_id from phones_table
where modeli_15
)
, i_15 as
(
Select * from phones_table
where model=i_15
)
Select * from i_15 left join non_i15 on
i_15.user_id=non_i15.user_id
where non_i15.user_id is null;
------------
2.Find users who have bought Iphone12 and upgraded to Iphone 15
Select user_id from phones_table
group by user_id
having count(iphone_model)= sum ( case when iphone_model=i-12 then 1 when iphone_model=i-15 then 1 else null)
-----------
3. Find users_count who have bought Iphone _15 for first time and user_count who have upgraded to Iphone_15 from earlier models
Output:
first time buyers count
Select count (case when iphone_Count=1 then user_id else null end as ) First_time_buyer_count ,
count (case when iphone_Count>1 then user_id else null end as ) First_time_buyer_count
from
(
Select user_id , count(distinct Iphone_model) as iphone_Count from phones_table
group by user_id
)
-------------
4. Give me only those users who have bought all the iphones model( #iphone lovers)
Additional table Product_dim contains all the Iphone_models
Select user_id , count( distinct iphone_model) from phones_table
group by user_id
having count( distinct iphone_model) = Select count(distinct product_dim) from product_dim
with cte as (
select distinct user_id , count(iphone_model) as cnt from iphone
group by user_id
)
select * from cte c inner join iphone i on c.user_id = i.user_id
where iphone_model = 'i-15' and cnt = 1
1st query (without cte/join/window function)🌟 user who bought iPhone 15 only:
SELECT user_id
FROM phones
WHERE iphone_model = 'i-15'
GROUP BY user_id
HAVING COUNT(DISTINCT iphone_model) = 1
- - AND COUNT(*) = 1 - - {Edit: I am not sure wether last line should be added. Wdy think @Ankit Bansal?}
Edit 2: Commenting Query 2: User who bought iPhone 12 and 15 only
"only small change to the initial query is adding 'IN' in 'WHERE' clasue, and changing "HAVING ...=1 to 2"
SELECT user_id
FROM phones
WHERE iphone_model IN ('i-12','1-15')
GROUP BY user_id
HAVING COUNT(DISTINCT iphone_model) = 2
Edit 3:
users who bought i-15 first time and user's who are upgrading from previous i phones to i-15 i.e; EVERYONE WHO HAS i-15
Bhai. Mae LinkedIn share se yaha hu. Mae experienced hu but in other domain Mujhe apki help chahiye koi contact hy apka .?
DM me on LinkedIn
SELECT
user_id,
phone_model
FROM VALUES (1, 'i-11'),(1, 'i-15'), (2, 'i-15'), (2, 'i-15') AS (user_id, phone_model)
WHERE (CASE WHEN phone_model = 'i-15' THEN 1 ELSE 0 END) = 1