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

[@akshaykodekal8483]

Very clearly and calmly explained

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

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

[@badrilalnagar9232]

Always smiling is a priceless quality of life.

[@gospelmoto2833]

I learned a new thing! Thanks!

[@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;

[@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;

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

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

[@prajju8114]

I used dense_rank and got the same output

[@RahulR-zm1ug]

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

[@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;

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

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