Walmart SQL Interview Question | SQL Window Functions | Advanced

[@kushmanthreddy4762]

with cte as( select *,max(transaction_date)over(partition by user_id) as recent_trasn from transactions3) select user_id,recent_trasn,count(*) as purchase_count from cte where transaction_date=recent_trasn group by user_id,recent_trasn order by recent_trasn

[@hairavyadav6579]

Nice explanations ,, bring more video related to data analyst role... Not only focus on sql,, if you make video related data analyst domain ,, your channel will grow and your way of explanation is very well and to attract more viewers also use Hindi to bring twist in video

[@atharvjoshi9959]

with cte as(select * , dense_rank() over (partition by user_id order by transaction_date desc) as rnk, count(product_id) over (partition by user_id order by transaction_date desc) as purchase_count from transactions) select user_id, transaction_date, purchase_count from cte where rnk=1

[@1112electronics]

Great video and awesome explanation..thanks .. keep it up...🎉

[@ishitvasingh9902]

We can also approach this by using Max agg function as a window function in cte and then call it out, with cte as ( select * , max(transaction_date) over (partition by user_id order by user_id) as recent_date from transactions) select transaction_date, user_id, count(product_id) as product_count from cte where transaction_date = recent_date group by 1,2 order by 1,2

[@yamanthakur7352]

I tried through subqueries and 2 possible solutions: sol1: select ROW_NUMBER() Over(partition by user_id order by user_id asc) Rn, user_id,count(product_id) productCount, transaction_date from transactions Where transaction_date IN(select MAX(transaction_date) from transactions group by user_id ) group by user_id, transaction_date -------------------------------------------------------------------------------------- sol2: select * from (select RANK() OVER(Partition by user_id Order by transaction_date desc) RANK, user_id,count(product_id) prodcount, transaction_date from transactions group by user_id,transaction_date) TN Where TN.RANK=1

[@gauravashish6605]

I have used this query select user_id,max(transaction_date), (select count(user_id) from transactions where user_id = a.user_id and transaction_date = max(a.transaction_date) group by user_id) as purchase_count from transactions a group by a.user_id order by max(transaction_date),USER_ID

[@heathens2867]

Shower query

[@chaitanyakadam5739]

Please continue this videos ❤❤❤❤

[@manjunathbukkapatnam3731]

Hi @nishitha Nagar Can you please also post the create and insert script for the scenario based questions

[@zerotohero510]

Nice Explanation 😊

[@echodelta7680]

Suppose there are two tables A and B. Each table has only two columns, ID and NAME. We have to get an output table that has rows in it as : 1st row from A 1st row from B 2nd row from A 2nd row from B 3rd row from A 3rd row from B .... and so on. What would be the query to generate this output table ?

[@TheSaahil006]

Add column id to both table. odd id to one table, even id to the second table... union both and order by id

[@echodelta7680]

​@@TheSaahil006 Thank you! I've been practicing complicated queries for so long that I forgot the simple things. There is another problem I am grappling with. Hope you can provide a simple solution for that too. Suppose a table EMP is as follows: ID DEPT 1 IT 2 NULL 3 NULL 4 HR 5 NULL 6 NULL How would we forward-fill the NULLs in column DEPT? I'm stuck with this problem since many days and the solutions I am coming across are not understandable to me. Kindly help.🙏

[@TheSaahil006]

@@echodelta7680 with cte as ( select id,dept,count(dept) over(partition by id order by id) as cliq from tab1 ) select id,dept,max(dept) over(partition by id,cliq) as new_dept from cte

[@chandufighter7667]

Your explanation is newt level post post daily videos

[@trendingthoughts6726]

❤❤❤ super videos

[@neejudeshwal6515]

Thanks Mam

[@hairavyadav6579]

And also bring a sql project which contain 15 questions 5 basic , 5 inter, 5 advn as per your experiences it give freshers some experiences

[@sagartayde1185]

with cte as( select *, dense_rank() over(partition by user_id order by transaction_date desc) flag from transactions ) select transaction_date, user_id, sum(flag) as purchase_count from cte where flag=1 group by transaction_date, user_id order by transaction_date

[@shubhamsharma8642]

select user_id,transaction_date,count(product_id) from (select *,rank()over(partition by user_id order by transaction_date desc)tp from transactionss)temp where tp=1 group by 1,2;

[@arjundev4908]

with cte as(SELECT *, dense_rank()over(partition by user_id order by transaction_date desc) as rw FROM transactions) select transaction_date,user_id,count(distinct product_id) as purchase_count from cte where rw = 1 group by 1,2;

[@sahasranamansriraman2309]

with user_transaction_cte as (select * , dense_rank() over (paartition by user_id order by transaction_date desc) as dense_rank from user_transactions), select transaction_date, user_id, count(user_id) as purchase_count from user_transaction_cte where dense_rank = 1 group by 1,2 select * from user_transaction_cte order by purchase_count ;

[@varunas9784]

Thank you for sharing! Here's my attempt on SQL server: ================================== with cte as (select *, LAST_VALUE(transaction_date) over(partition by user_id order by user_id) [latest transaction], COUNT(product_id) over(partition by user_id, transaction_date order by transaction_date) [Count of products] from transactions11) select distinct transaction_date, user_id, [Count of products] from cte where transaction_date = [latest transaction] ==================================

[@akshaykodekal8483]

Clearly explained

[@Nishikant_N]

Hi, Nishtha let me know is this Correct? SELECT user_id, transaction_date, Count(product_id) as Purchase_Count FROM ( SELECT product_id, user_id, spend, transaction_date, DENSE_RANK() OVER (PARTITION BY user_id ORDER BY transaction_date DESC) AS Latest_Date FROM Transactions ) AS RankedTransactions WHERE Latest_Date = 1 Group By user_id, transaction_date

[@KapilKumar-hk9xk]

with cte as (select *, dense_rank() over(partition by user_id order by date(transaction_date) desc) as dr from transactions) select transaction_date, user_id, count(product_id) as purchase_count from cte where dr = 1 group by transaction_date, user_id;

[@HimanshuSingh-cf7wr]

with m as ( select dense_rank() over (partition by user_id order by transaction_date desc) as ds_rnk, km.* from transactions as km) select distinct transaction_date,user_id,count(product_id) over (partition by user_id) as purchase_count from m where ds_rnk=1

[@MitaliNeerPatel]

Thank you , helpful

[@aditi_shetti]

My answer : with cte as (select product_id, user_id , transaction_date , rank() over (partition by user_id order by transaction_date desc) as rn from transactions) select user_id , transaction_date, count(product_id) as prod_count from cte where rn = 1 group by user_id , transaction_date

[@vikassolanki297]

With cte as( select distinct user_id, max(transaction_date) over( partition by user_id order by user_id) as latest_date from ttransactions) select latest_date, cte.user_id,count(product_id) from cte inner join ttransactions on cte.latest_date = ttransactions.transaction_date and cte.user_id = ttransactions.user_id group by latest_date,cte.user_id

[@junkjunk7819]

No CTE nor subquery required. TC = amortized O of logN since internal implementation of max is done using minHeap algo due to order_by clause. im worst case scenario TC is O of n(with heapify) or nlogn(for single loading) SC = O of N as internally the data is stored in a tree or a heap SELECT MAX(transaction_date) OVER (PARTITION BY user_id) AS transaction_date, DISTINCT user_id, COUNT(product_id) OVER (PARTITION BY user_id ORDER BY transaction_date DESC LIMIT 1) AS purchase_count FROM transactions ORDER BY 1 asc;

[@BabaiChakraborty-ss8pt]

My Answer with recent_cte as ( select *, rank() over (PARTITION BY user_id ORDER BY transaction_date desc ) as recent_time from transactions order by user_id ) SELECT transaction_date, user_id, COUNT(product_id) AS purchased_counts FROM recent_cte WHERE recent_time = 1 GROUP BY transaction_date, user_id ORDER BY transaction_date;

[@hairavyadav6579]

My approach select transaction_date,user_id,purchase_count from (select *,count(*) over(partition by user_id order by cast(transaction_date as date) desc) as purchase_count,row_number() over(partition by user_id order by cast(transaction_date as date) desc) rnk from transactions) sal where rnk = 1;

[@Alexpudow]

MS SQL approach SELECT TOP 3 transaction_date, user_id, count(product_id) FROM transactions GROUP BY transaction_date, user_id ORDER BY 1 DESC

[@talkingtomsongs5791]

with purchase_count_per_date as ( select transaction_date, user_id, count(*) as purchase_count from transactions group by transaction_date, user_id order by transaction_date desc limit 3) select * from purchase_count_per_date pcd order by pcd.purchase_count;

[@sachinn5503]

WITH CTE AS( SELECT USER_ID,COUNT(USER_ID) C, DATE(TRANSACTION_DATE) D FROM TRAN GROUP BY USER_ID,D ORDER BY USER_ID,D DESC), H AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY USER_ID ORDER BY D DESC) RECENT FROM CTE) SELECT USER_ID,C,D FROM H WHERE RECENT =1;

[@astasingh]

sql server my solution:- select transaction_date,user_id,count(user_id) from ( select user_id,transaction_date,dense_rank() over (partition by user_id order by transaction_date desc) ct from transactions) e where ct=1 group by user_id,transaction_date order by transaction_date

[@Chathur732]

select transaction_date,user_id,count(user_id) as purchase_count from ( select *, rank() over(partition by user_id order by transaction_date desc) as rn from transactions ) where rn = 1 group by transaction_date,user_id

[@user-gq6cg3ls7f]

another approach select transaction_date, user_id, count(*) purchase_count from transactions_Thu where transaction_date in( select max(transaction_date) transaction_date from transactions_Thu group by user_id) group by transaction_date, user_id order by transaction_date

[@hrishi2839]

select max(transaction_date), user_id, rank() over(partition by max(transaction_date) order by user_id ) from transactions group by user_id order by user_id

[@dhruvdhandhukiya8027]

select a.transaction_date, a.user_id, a.purchase_count from ( select transaction_date, user_id, count(product_id) as purchase_count, max(transaction_date) over (partition by user_id) as max_transaction_date from transactions_new group by (transaction_date), user_id order by transaction_date ) a where transaction_date = max_transaction_date

[@shgg5996]

SELECT transaction_dt, user_id, COUNT(product_id) AS product_count FROM user_transaction GROUP BY transaction_dt, user_id ORDER BY transaction_dt DESC LIMIT 1;

[@SnapMathShorts]

with cte as ( select * , rank() over(partition by user_id order by transaction_date desc) as rnk from transactions) select transaction_date,user_id, count(*) as purchase_count from cte where rnk = 1 group by 1,2 order by 3