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
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
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;
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;
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
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 ) );
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.
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;
Very clearly and calmly explained
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
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
I learned a new thing! Thanks!
Always smiling is a priceless quality of life.
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;
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;
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
it is the third transaction partition by id where order by ?
Thanks for the script
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 ) );
I used dense_rank and got the same output
Hey you contant very useful but you more details SQL full course and main domain use it company details python
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.
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!
@@datasciencewithnish
Thanks ...
Sometimes it happens by mistake....
But thank you for making valuable content
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;
Can u do it by CTE
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