Walmart SQL Interview Question | SQL Window Functions | Advanced

Подписаться 2,4 тыс.

Просмотров 1,9 тыс.

50% 1

In this video, we walk through a SQL query to retrieve Walmart users' most recent transaction date, user ID, and the total number of products they purchased. Learn how to efficiently sort data in chronological order and calculate product counts based on user transactions.
Table: user_transactions
CREATE TABLE transactions (
product_id INT,
user_id INT,
spend DECIMAL(10, 2),
transaction_date DATETIME
);
INSERT INTO transactions (product_id, user_id, spend, transaction_date)
VALUES
(3673, 123, 68.90, '2022-07-08 10:00:00'),
(9623, 123, 274.10, '2022-07-08 10:00:00'),
(1467, 115, 19.90, '2022-07-08 10:00:00'),
(2513, 159, 25.00, '2022-07-08 10:00:00'),
(1452, 159, 74.50, '2022-07-10 10:00:00'),
(1452, 123, 74.50, '2022-07-10 10:00:00'),
(9765, 123, 100.15, '2022-07-11 10:00:00'),
(6536, 115, 57.00, '2022-07-12 10:00:00'),
(7384, 159, 15.50, '2022-07-12 10:00:00'),
(1247, 159, 23.40, '2022-07-12 10:00:00');
#sql #dataanalysts #dataengineer #dataanalysis #interviewquestion #sqlinterview

Опубликовано:

27 сен 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 16

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

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

@atharvjoshi9959 2 дня назад

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

@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

@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

@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

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

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

@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

@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

@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