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

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

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

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

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

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

[@1112electronics]

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

[@zerotohero510]

Nice Explanation 😊

[@chaitanyakadam5739]

Please continue this videos ❤❤❤❤

[@trendingthoughts6726]

❤❤❤ super videos

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

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

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

[@neejudeshwal6515]

Thanks Mam

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

[@chandufighter7667]

Your explanation is newt level post post daily videos

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

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

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

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

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

[@manjunathbukkapatnam3731]

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

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

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

[@akshaykodekal8483]

Clearly explained

[@MitaliNeerPatel]

Thank you , helpful

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

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

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

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

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

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

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

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

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

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