Uber SQL Interview Question | Find User's third transaction | SQL Window Functions | Advanced SQL

[@shettygarusairam166]

Your explanation is clear

[@akshaykodekal8483]

Very clearly and calmly explained

[@gospelmoto2833]

I learned a new thing! Thanks!

[@badrilalnagar9232]

Always smiling is a priceless quality of life.

[@prajju8114]

with cte_trans as ( select [user_id],[spend],[transaction_date],dense_rank() OVER(partition by user_id order by transaction_date) as transaction_s from transactions ) Select * from cte_trans where transaction_s=3

[@yousfimohamed3999]

with cte as (select *,row_number() over(partition by user_id order by transaction_date ) as rn from transactionsss ) select user_id,spend,transaction_date from cte where rn = 3

[@AnandBabu-e5n]

with uber as ( select *, row_number() over(partition by id) as rn from uberdata ) select * from uber where rn % 3 =0; I think we need to fetch every 3 rd transaction in table. may be we need to modify like above

[@astech4407]

it is the third transaction partition by id where order by ?

[@ajaycheryala7760]

Thanks for the script

[@tilu391]

with cte as (select user_id,spend,transaction_date,rank() over(partition by user_id order by transaction_date) as rnk from transactions) select user_id,spend,transaction_date from cte where rnk =3;

[@aditi_shetti]

My take on the question where I have used dense_rank instead of row_number with cte1 as ( select user_id, spend, transaction_date , dense_rank() over(partition by user_id order by transaction_date) as dense_rn from transactions_1 ) select user_id, spend, transaction_date from cte1 where dense_rn = 3

[@luckilygamer5462]

My Approach: select user_id,spend,transaction_date from( select user_id, spend, transaction_date, row_number() over( partition by user_id order by transaction_date) as rnk from transactions) asa where rnk=3

[@Savenature635]

with cte as (select *,dense_rank() over(partition by user_id order by transaction_date asc) as rnk from uber_transactions) select user_id,spend,transaction_date from cte where rnk=3;

[@prajju8114]

I used dense_rank and got the same output

[@Spiritual_Talks2001]

third transaction of every user: Select * From (Select t1.user_id From Transaction t Where 3= (Select Count(DISTINCT (t2. user_id)) from Transaction where t2.user_id>=t1.user_id group by user_id ) );

[@RahulR-zm1ug]

Hey you contant very useful but you more details SQL full course and main domain use it company details python

[@hairavyadav6579]

i think script is wrong the your date format is year/date/month because i facing problem at the time of inserting value. so please correct this thing in correct format in mysql fromat is -- year/month/date.

[@datasciencewithnish]

You're correct. The date format in MySQL should be year/month/day. I’ve corrected the script to follow the correct format. Big thanks for noticing it!

[@hairavyadav6579]

@@datasciencewithnish Thanks ... Sometimes it happens by mistake.... But thank you for making valuable content

[@samyukthashanmugam7516]

WITH cte AS ( SELECT user_id, spend, transaction_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY transaction_date ASC) AS user_trans FROM transactions ) SELECT * FROM cte WHERE user_trans = 3 ORDER BY user_id;

[@theinsightminer08]

SELECT user_id, spend, transaction_date FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY transaction_date) AS rn FROM transactions) subquery WHERE rn = 3;

[@MubarakAli-qs9qq]

Can u do it by CTE

[@AnandBabu-e5n]

with uber as ( select *, row_number() over(partition by id) as rn from uberdata ) select * from uber where rn % 3 =0; Please find the cte expression