select user_id,extract(year from transaction_date) as year ,round(avg(transaction_amount),0) as average_transaction_amount from transactions Group by user_id,year

SELECT MONTH(SUBMIT_DATE) MONTH,RESTAURANT_ID,ROUND(AVG(RATING),1) AVG_RATING FROM REVIEWS WHERE (RESTAURANT_ID,MONTH(SUBMIT_DATE)) IN (SELECT RESTAURANT_ID,MONTH(SUBMIT_DATE) MONTH FROM REVIEWS GROUP BY RESTAURANT_ID,MONTH HAVING COUNT(*) >= 2) GROUP BY MONTH,RESTAURANT_ID ORDER BY AVG_RATING DESC

select num from (select num,rnk,count(rnk) cnt_rnk from ( select *,id - row_number() over(partition by num order by num) rnk from logs) a group by num,rnk having cnt_rnk >= 3) a;

WITH cte AS ( SELECT restaurant_id, EXTRACT(MONTH FROM submit_date) AS month, AVG(rating) AS avg_rating, COUNT(review_id) AS review_count, DENSE_RANK() OVER (PARTITION BY restaurant_id ORDER BY AVG(rating) DESC) AS rn FROM reviews GROUP BY restaurant_id, month ) SELECT month, restaurant_id, avg_rating FROM cte WHERE rn = 1;

with cte as ( select transaction_date,user_id,product_id,rank() over(partition by user_id order by transaction_date DESC) as transaction_rank from transactions) select transaction_date,user_id,COUNT(transaction_rank) as purchase_count from cte where transaction_rank=1 group by user_id,transaction_date order by transaction_date

WITH cte AS ( SELECT card_name, max(issued_amount) AS max_amount from credit_card_issuance GROUP BY card_name) , cte_2 AS ( SELECT card_name , MIN(issued_amount) AS min_amount FROM credit_card_issuance GROUP BY card_name) SELECT cte.card_name , max_amount , min_amount-- AS diff FROM cte INNER JOIN cte_2 ON cte.card_name = cte_2.card_name;

select month(transaction_Date) as month, sector , avg(revenue) as avge from transactions t join sectors s on t.company_id=s.company_id group by month(transaction_date) , sector

with cte as ( select artist_name ,dense_rank() over(order by count(s.song_id) DESC) as artist_rank from artists as a inner join songs as s on a.artist_id=s.artist_id inner join global_song_rank as g on s.song_id=g.song_id where g.`rank`<=10 group by a.artist_name ) select artist_name,artist_rank from cte limit 5

With Third_ranked_employee as (select employee_id ,concat(first_name," ",last_name) AS Full_name ,round(salary ,0) AS salary , job_category ,dense_rank() over(partition by job_category order by salary desc) as rnk from employees) select employee_id,Full_name,job_category from Third_ranked_employee where rnk=3 order by employee_id asc

Interview questions you post video please others SQL basic formula and query data analytics video

with cte as( select num, lead(num,1) over (order by num) as next_num, lead(num, 2) over (order by num) as sec_next_num from logs) select distinct num from cte where num=next_num and num=sec_next_num;

my approach : select seller_name from sellers s left join a_orders t on s.seller_id=t.seller_id where s.seller_id not in (select distinct seller_id from a_orders where year(sale_date)=2020) order by seller_name ;

my approach : with cte as (select id,num as current_num,lag(num,1,1) over(order by id) as prev_num, lead(num,1,1) over(order by id) as next_num from logs) select current_num as consecutive_nums from cte group by current_num,prev_num-current_num having count(1)>=3;

SELECT order_id,CASE WHEN order_id%2!=0 THEN LEAD(item,1,item) OVER (ORDER BY order_id) WHEN order_id%2=0 THEN LAG(item) OVER (ORDER BY order_id) END AS ITEMS FROM DBO.orders;

select year(transaction_date) as year, user_id, avg(transaction_amount) as avg_amount from transactions group by year(transaction_date), user_id;

How to split the string in sql? Can any one help me

WITH CTE AS( SELECT DEL_PARTNER,timestampdiff(MINUTE, ORDER_TIME,DELIVER_TIME) TIME_TOOK ,PREDICTED_TIME, CASE WHEN timestampdiff(MINUTE, ORDER_TIME,DELIVER_TIME)>PREDICTED_TIME THEN 'DELAY' ELSE 'ONTIME' END AS STATUS FROM ORD) SELECT DEL_PARTNER, SUM(CASE WHEN STATUS='DELAY' THEN 1 ELSE 0 END) DELAYCOUNT, SUM(CASE WHEN STATUS='ONTIME' THEN 1 ELSE 0 END) ONTIMECOUNT FROM CTE GROUP BY DEL_PARTNER;

with cte as( select * ,case when LEAD(order_id) over (order by order_id) % 2=0 then LEAD(order_id) over (order by order_id) when LAG(order_id) over (order by order_id) % 2=1 then LAG(order_id) over (order by order_id) else 0 end as New_OrderId from orders where order_id<>(select MAX(order_id) from orders) ) select New_OrderId,ITEM FROM cte union all select order_id,item from orders where order_id=(select MAX(order_id) from orders)

WITH CTE AS( SELECT *, AVG(TRANSACTION_AMOUNT) OVER(PARTITION BY TRANSACTION_DATE ORDER BY user_id) AVGT FROM TRANSAC) select year(transaction_date) year,user_id,round(avgt,0) avgpy from cte order by year ;

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;

with cte as( select e.name empname,e.department,w.name managername from emp e join emp w on e.managerid=w.id), manager as( select *, count(*) over(partition by managername) number_of_reporting from cte) select department,managername,number_of_reporting from manager where number_of_reporting>=6 limit 1;

with cte as (select * from transactions order by company_id,transaction_date), l as( select *, avg(revenue) over(partition by company_id order by transaction_date) avgrevenue from cte), t as( select *, row_number() over(partition by company_id order by transaction_date) rn from l) select t.company_id,sector,avgrevenue from t join sectors ss on t.company_id=ss.company_id where rn =2;

My approach: SELECT card_name, max(issued_amount)- min(issued_amount) as difference from monthly_cards_issued group by card_name order by difference desc

My Approach: select user_id,spend,transaction_date from( select user_id, spend, transaction_date, row_number() over( partition by user_id order by transaction_date) as rnk from transactions) asa where rnk=3

my approach: SELECT order_id, CASE WHEN order_id % 2 = 1 THEN COALESCE(lead(item) over (), item) ELSE lag(item) over () END AS item FROM orders

Hi Nishtha, Please do more videos on this

with cte as ( select * ,order_id-1 id1 from orders2 where order_id%2=0 union all select *,order_id+1 id2 from orders2 where order_id%2<>0 ) select row_number() over(order by id1) order_id,item from cte

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;

select card_name,max(issued_amount) -min(issued_amount) as Difference from credit_card_issuance group by card_name

Hi Nishtha, Question stated to find the difference between number of issued cards between the month of Highest issuance cards and Lowest Issuance card, but, you have calculated difference between the amount between the month of Highest issuance cards amount and lowest issuance card amount. i feel both of them are not same.

Select distinct(card_name),max(issued_amount)_min(issued_amount) From monthly_card_issued Group by card_name Order by differenxe desc,

Why this is not in the playlist

my approach was simple select e1.`name`as Manager,e2.department,count(e2.managerId) as employee_Reporting from employee e1 join employee e2 on e1.id = e2.managerId group by e1.`name`,e2.department having count(e2.managerId) >=5;

Good 🎉😊

Thanks Mam

Thanks Mam

1st view❤

Hi Mam select card_name, max(issued_amount) - min(issued_amount) as diff_amnt from credit_card_issuance group by card_name order by max(issued_amount) - min(issued_amount) desc

Not maximum number of and minimum number of its maximum amount and minimum amount. When you are using that term bit confusing 😊

How the avg sales amount will calculate for Jan Feb months ? How we get put 100 and 225 can u give exp. For this?

I've been watching your video for the past 4 months. You're helping the people who want to enter the field of data analysis. Great Work Go ahead.

;with cte_creditcard as ( select card_name, issued_amount, issue_month, issue_year, MAX(issued_amount) OVER (PARTITION BY card_name) as max_amount, MIN(issued_amount) OVER (PARTITION BY card_name) as min_amount from credit_card_issuance ) select distinct card_name, max_amount - min_amount as difference_amount from cte_creditcard

my approach is with cte as (select count(*) as art_count,a.artist_name from artists a join songs s on s.artist_id = a.artist_id join global_song_rank g on g.song_id = s.song_id group by a.artist_id) select dense_rank() over ( order by art_count desc) as rn ,artist_name from cte;

select YEAR(transaction_date) as year ,user_id, avg(transaction_amount) as average_transaction_amount from transactions group by user_id,YEAR(transaction_date)

SELECT YEAR(transaction_date) AS year, user_id, AVG(transaction_amount) AS avg_transaction_amount FROM transactions GROUP BY year,user_id;

With cte1 as( select a.customer_id,a.product_id,b.product_category from customer_contracts a inner join products b on a.product_id=b.product_id order by a.customer_id) select customer_id from cte1 group by customer_id having count(distinct product_category)=3

select employee_id,first_name,job_category from (select employee_id,first_name,salary,job_category, dense_rank()over(partition by job_category order by salary desc) as rn from employees) abc where rn=3 order by employee_id

nice explanation

Is same question available on stratascratch?

My approach with cte1 as ( select card_name,max(issued_amount) as max,min(issued_amount) as min from credit_card_issuance group by card_name) select card_name,max-min as difference from cte1 group by card_name ;