Episode 1 : Crazy SQL Interview | iPhone 15 Fever

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

[@ankitbansal6]

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

[@vishalmane3139]

Do u recommend taking notes while learning thru namastesql ?

[@ankitbansal6]

@@vishalmane3139 not required. Notes files are available as attachment

[@nayanjyotibhagawati939]

​@@ankitbansal6for the 1st question. Can we write the below Select * from user where iPhone_model like '%12' and like '%15'

[@kashmirshadows8150]

This is SO SO WONDERFUL Please come up with more such videos

[@ladiashrith5230]

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

[@manoj_mj0715]

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');

[@shubhangidhale7942]

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.

[@piyushpawar75]

For third problem when user shift from any model to i-15 we can solve it easily by window function and self join

[@user-uh2cs7uy7r]

Please bring more such interview content

[@ChinweOge]

You are amazing 🤩

[@sureshydv724]

Hi @ankitbansal I think in 1st question on the 2nd solution we need to replace a 0 with null otherwise 0 is count as a row or repalce the count with sum agg-function to get the desired output. And on the 1st solution remove the couth which is written first next to the * otherwise again we will not get the desired output. Thanks & Regards Suresh

[@apna9656]

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

[@shubhangidhale7942]

4th Query select user_id from phones group by user_id having count(distinct phones. iphone_model)= (select count( distinct product_dim) from products);

[@livelovelaugh4050]

Thank you so much for this series. Waiting for next one

[@ankitbansal6]

More to come!

[@himanshuparihar9888]

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

[@himanshuparihar9888]

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

[@devarapallisaranyarani6979]

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

[@riteshbali1405]

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'

[@himanshuparihar9888]

4th one select user_id from phones group by user_id having count(*) = (select count(*) from phone_dim)

[@anujparmar2892]

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")

[@srinidhimurali644]

- 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

[@rishavraj3867]

select user_id from phones group by user_id having count(iphone_model) =1 and iphone_model = i-15; is it correct?

[@kishorkhengare]

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

[@santoshatyam1409]

For first one we can use except operator right

[@Tusharchitrakar]

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)

[@kishorkhengare]

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.

[@Cplusplus745]

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.

[@vertikaagarwal7921]

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

[@Piyush-vu2fg]

Bhai simple si baat hai…. 1st query : SELECT user , COUNT (phonemodel) AS cnt FROM PhoneTable GROUP BY user HAVING cnt < 2;

[@fog2640]

finished watching

[@rahulmehla2014]

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"

[@ghostriderik7445]

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

[@shubhangidhale7942]

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';

[@theenfieldertales]

I thought of using LAG() function in second query.

[@sandeepguptha6440]

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 😅

[@shubhamagarwal2321]

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

[@SnapMathShorts]

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)

[@JohnvictorPaul-ec1sm]

select user_id, count(distinct iphone_model) cn from phones_table group by user_id having cn=5; last answer

[@mohakjain1404]

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)

[@krishnajaiswal7650]

this thing also comes to my mind when saw the video @ankitbansal6

[@shorakhutte1887]

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);

[@himanshuparihar9888]

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' ) )

[@Muralidhar-gu3bt]

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);

[@akashv6905]

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);

[@ritwikpahwa5311]

In second query, user id 2 will also qualify. Correct me if i am wrong.

[@JohnvictorPaul-ec1sm]

select user_id,iphone_model from phones_table where iphone_model='i-15' and user_id not in(select user_id from phones_table group by user_id having count(1)>1)

[@user-uh2cs7uy7r]

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';

[@shrabannaik5927]

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'

[@riteshgaihre6277]

right right

[@ss-hm6ey]

Please crct me if i am wrong... My query for findimg users who bought only iphone 15 : With cte as ( select user_id from iphone Group by user_id Having count(*)

[@harshitsingh28]

--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

[@SHERKSTOLEURTOES]

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

[@ankitbansal6]

Right 👍

[@randomfun6431]

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!!

[@BillionDollerDream]

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?

[@gauraavthakurr]

No

[@bhaskaravenkatesh6994]

First_value(iPhone model) over ( partition by user_id) = 'i-15'

[@HimanshuKumar-rw3dn]

SELECT user_id FROM phone_table where user_id not in (Select user_id from phone_table where phone_model'i-15');

[@atifsuhail7803]

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)

[@ankitbansal6]

You are right. Either we should change the count to sum or make it null as you suggested.

[@PiyushSharma-jv4fy]

Spot On 👍🏼 Even I was searching for the comment where someone mentions about SUM but even NULL would do the job.

[@PiyushSharma-jv4fy]

Ok , on 18:30 sec it is corrected .

[@vineetjain7518]

yes @@ankitbansal6 but why 0 is giving error

[@himanshuparihar9888]

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')

[@vaibhavverma1340]

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'

[@user-ko7yz9kj4o]

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

[@Thetradersclub_]

Sir bring more episodes of mock interviews please.

[@ankitbansal6]

Sure

[@kailashpatro5768]

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

[@grim_rreaperr]

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;

[@abhishekmohanty9971]

Ankit, in the second query user 2 will also qualify. Kindly could you have a look into it.

[@ranadeepghosh1891]

Yes

[@nikunjmistry373]

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

[@nachi1588]

Every time he said Nachiket It felt like he was talking to me. 😹

[@Rumiesque]

Crazy ho gaya Bhai yeh toh

[@randomfun6431]

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

[@weshallbe]

Hi, Ankit! Can I pleaaaase take part in this next time? I'm a fresher. I want a REALITY CHECK of where i stand.

[@ankitbansal6]

Sure . Send me dm on LinkedIn

[@RajatKumar-ys4yg]

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)

[@aaravkumarsingh4018]

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);

[@abdulqadar559]

in having clause we should use aggregate functions right. in your solution you are directly checking the column value

[@jaswanth333]

q2 having should be shifted to where clause

[@adharshsunny5154]

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

[@AkashKumar-mb4pd]

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

[@narendersingh2851]

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

[@aadil3638]

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 😂😂😅😅

[@ankitbansal6]

Haha 😆 way to go

[@user-fc8dz5kn2y]

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);

[@SandeepGupta-ye8ik]

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

[@user-ko7yz9kj4o]

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

[@Op-Buyer]

Bhai. Mae LinkedIn share se yaha hu. Mae experienced hu but in other domain Mujhe apki help chahiye koi contact hy apka .?

[@ankitbansal6]

DM me on LinkedIn

[@srinivasareddy3709]

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

[@proteetisaikia1811]

Tried the same in Oracle: SELECT user_id FROM( SELECT user_id, LISTAGG(iphone,'|')WITHIN GROUP(ORDER BY ROWID) rn FROM iphone GROUP BY user_id ) WHERE rn LIKE 'i-15%';