Видео

solution using pyspark from pyspark.sql.window import Window win=Window.partitionBy('user_id').orderBy(col('transaction_date').desc()) walmart_df=walmart_df.withColumn('rank',rank().over(win)) walmart_df=walmart_df.where(col('rank')==1) walmart_df1=walmart_df.groupBy('user_id','transaction_date').agg(count('*').alias('purchase_count')).orderBy(col('transaction_date')) walmart_df1.show(truncate=False)

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;

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

Nicely explained video. I have an approach with more advanced funtions i.e, lead and lag, SELECT order_id, CASE WHEN order_id % 2 = 1 THEN COALESCE(lead(item) over (ORDER BY order_id), item) ELSE lag(items) over (ORDER BY order_id) END AS item FROM orders

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;

Nice..Thanks for your efforts..

SELECT num AS consecutive_nums FROM logs GROUP BY num HAVING COUNT(DISTINCT id) >=3 AND MAX(id) - MIN(id) =2;

postgresql : with cte as (select *, (id - row_number() over(partition by cnt order by id )) rn from (select * ,count(*) over(partition by num order by id range between unbounded preceding and unbounded following) cnt from logs)) select distinct num from (select *, count(rn) over(partition by rn order by id range between unbounded preceding and unbounded following) final_cnt from cte) where final_cnt >=3

God's love is reserved only for the virtuous and the dutiful.

Please make video on SQL in detail

SELECT TRANSACTION_DATE ,USER_ID,COUNT(PRODUCT_ID) PURCHASE_COUNT FROM ( SELECT PRODUCT_ID,USER_ID,TRANSACTION_DATE, DENSE_RANK() OVER(PARTITION BY USER_ID ORDER BY USER_ID,TRANSACTION_DATE DESC) RN FROM transactions) WHERE RN=1 GROUP BY USER_ID, TRANSACTION_DATE;

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

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;

This question asked for fresher or experience

with cte as( select *, LAG(num) over (order by id) pre_row, LEAD(num) over (order by id) next_row from logs ) select num as consecutive_number from cte where (num=pre_row) and (pre_row=next_row)

Please mam ,, Bring one project on real scenario based , with 15 questions ..

select distinct l1.num from logs l1 join logs l2 on l1.id=l2.id-1 join logs l3 on l1.id=l3.id-2 where l1.num = l2.num and l2.num = l3.num;

My approach select distinct num from (select *,lead(num) over(order by id) as l1 , lead(num,2) over(order by id) as l2 from logs) sal where num = l1 and l1=l2;

WITH cte AS ( SELECT id, num, LEAD(num) OVER(ORDER BY id) AS next_num FROM logs ), cte2 AS ( SELECT num, (next_num - num) AS diff FROM cte ) SELECT num, COUNT(diff) AS cnt FROM cte2 WHERE diff IS NOT NULL GROUP BY num; My approach little bit different I used only CTE's and Count(), group By

Thoda bhut Hindi language use kar doge sarkar??😅

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

Hi is there any way you could do that without creating those extra tables?

Another approach to solve it is as below.. select Num as consecutive_num from ( select *, LEAD(Num,1,Num) over(order by id) as next_num, LEAD(Num,2) over(order by id) as next_to_next_num, case when Num = LEAD(Num,1,Num) over(order by id) and Num = LEAD(Num,2) over(order by id) then 1 else 0 end as flag from Logs ) x where x.flag = 1

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 distinct a.artist_name,s.artist_id,count(g.song_id) as number_of_appearances from artists a join songs s on a.artist_id=s.artist_id join global_song_rank g on s.song_id=g.song_id where g.rankk<=10 group by 1,2),cte_2 as ( select *,dense_rank() over(order by number_of_appearances desc) as artist_rank from cte) select artist_name,artist_rank from cte_2 where artist_rank<=5;

My approach in MySql : with cte as (select *,month(submit_date) as monthh,count(rating) over(partition by month(submit_date),restaurant_id) as count_rating from reviews) select monthh,restaurant_id, round((sum(rating))/(count(rating)),1) as average_rating from cte where count_rating>=2 group by 1,2 order by restaurant_id,monthh;

It's my solution which is lengthy compared to video explanation just because of i don't know about TIMESTAMPDIFF function WITH cte3 AS ( SELECT *, HOUR(TIME(deliver_time - order_time)) * 60 + MINUTE(TIME(deliver_time - order_time)) AS Actual_time FROM order_details ), cte4 AS ( SELECT *, CASE WHEN Actual_time > predicted_time THEN 'Delayed' ELSE 'On Time' END AS delivery_cat FROM cte3 ) SELECT del_partner, COUNT(delivery_cat) FROM cte4 WHERE delivery_cat = 'Delayed' GROUP BY del_partner; Please give feedback if you like..

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

with cte as ( select category,PRODUCT, sum(spend) as total_spend from ProductSpend group by category,PRODUCT ---order by category,PRODUCT ), cte2 as ( select *, RANK() over(partition by category order by total_spend desc) as rankings from cte ) select category,PRODUCT,total_spend from cte2 where rankings in (1,2)

select count(company_id) as duplicate_companies from ( select company_id, title, description,count(*) as cnt from job_listings group by company_id, title, description) tbl where tbl.cnt >1

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 ?

According to the discovery of scientists, the most amazing thing in the universe is the human brain.

here is my solution : select seller_name from sellers where seller_name not in ( select s.seller_name as sellername from orders o join sellers s on o.seller_id = s.seller_id and Year(o.sale_date) =2020 group by s.seller_name )

change varchar 255 to in 25 in case your output is weird: with cte as ( select order_id, lead(item,1,item) OVER(order by order_id) as lead_item, lag(item) OVER(order by order_id) as lag_item from orders) select order_id, case when order_id%2=0 then lag_item else lead_item end as item from cte;

select month(submit_date) as month, restaurant_id, format(avg(rating*1.0),'0.0') as avg_rating from reviews group by month(submit_date), restaurant_id having count(review_id) >=2

here is my solution : with cte as ( select * , cast(DATEDIFF(Minute, order_time,deliver_time) as int) as actual_time from order_details ),cte2 as( select * , case when actual_time>predicted_time then 1 else 0 end as flag from cte ) select del_partner, count(*) as order_count from cte2 where flag =1 group by del_partner

What is the order of processing in following line? DENSE_RANK() OVER(PARTITION BY department ORDER BY salary DESC) Does the partitioning of departments happen first, followed by ordering of salaries within those partitions and finally ranking of rows? Or does it happen in some other order? Kindly enlighten.

In dataset, the column name is 'order_date' not 'date'. Anyway, good example.

Hi Nishtha, 1. If there are more than 1 employee in each dept. having same salary as third highest salary, then what result would your query return? 2. Say, there is a table TRANSACTION having two columns ID and BILL. Table has 100 records, with IDs repeated many times, each ID having different or same bills. Here's a SELECT query to find the avg. bill for each ID over the table : SELECT ID, AVG(BILL) FROM TRANSACTION GROUP BY ID; My question is, does the AVG function run 100 x 100 times, top to bottom scanning one ID at a time? Note that I am not asking about the order of query execution (I know the order). I'm asking how these functions operate on the table rows in the background.

select customer_id, count(product_category) cnt from customer_contracts cc join products p on p.product_id = cc.product_id group by 1 having count(product_category) = (select count(distinct category) totl_ctgry from products)

with cte as (select artist_name, count(1) cnt from (select artist_name, s.song_id , a.artist_id, rank from artists a join songs s on s.artist_id = a.artist_id join global_song_rank gr on gr.song_id = s.song_id where rank <= 10) x group by 1) select artist_name , dense_rank() over(order by cnt desc) rnk from cte

select restaurant_id, mnt, round(avg(rating),1) avgs from (select *, extract(month from submit_date) mnt from reviews) x group by 1,2 having count(rating) >= 2 order by 2,1

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

thank you amazing explanation

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

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;

Here is my solution with cte as( select tbl.artist_id,a.artist_name, count(*) as cnt from artists a join ( select s.song_id,s.artist_id from global_song_rank gl join songs s on gl.song_id = s.song_id and gl.rank <=10 --order by s.artist_id ) tbl on a.artist_id = tbl.artist_id group by tbl.artist_id,a.artist_name ) select *, DENSE_RANK() over(order by cnt desc) as rank from cte

Nice explanation ,,

in postgresql: with cte as (select * ,(start_date - lag(start_date,1, start_date-1) over(order by task_id)) flag from project), flags as (select * ,(sum(flag) over(order by task_id) - task_id) grp from cte), final as (select distinct min(start_date) over(partition by grp ) start_date, max(end_date) over(partition by grp ) end_date, (max(end_date) over(partition by grp ) -min(start_date) over(partition by grp ) ) date_diff from flags) select start_date, end_date from final order by date_diff

Select q.*, case when n.npv is not null then n.npv else 0 end as npv from queries as q left join npv as n on q.id=n.id and q.year=n.year