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

SELECT DATE_FORMAT(t.transaction_date,'%m') AS MONTH,s.sector ,AVG (t.revenue) FROM Transactions t JOIN Sectors s ON t.company_id=s.company_id GROUP BY s.sector, DATE_FORMAT(t.transaction_date,'%m') ;

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 ;

select card_name,max(issued_amount) - min(issued_amount) as differences from credit_card_issuance group by card_name; Mam Please bring few question related to pandas also, Thank You so much for content ......

select artist_name,dense_rank()over(order by cnt desc) as artist_rank from (select artist_name,cnt,row_number()over(order by cnt desc) as rn from (select x.artist_name,count(y.artist_id) as cnt from artists x inner join (select a.artist_id,b.song_id from songs a inner join global_song_rank b on a.song_id=b.song_id where b.rank1<=10 )y on x.artist_id=y.artist_id group by x.artist_id) abc) xyz

My Approach: select year(transaction_date) as years,user_id, round(avg(transaction_amount),0) as avg_transaction_amount from transactions where year(transaction_date) between 2018 and 2022 group by user_id, year(transaction_date);

My approach without cte : select extract(year from transaction_date) as year, user_id, round(avg(transaction_amount),0) as average_transaction from amex_transactions where extract(year from transaction_date) between 2018 and 2022 group by 1,2;

My approach with cte as (select extract(YEAR FROM transaction_date) as Year, user_id,transaction_amount from transactions) select Year,user_id,avg(transaction_amount) as avg_transaction_amount from cte group by Year, user_id;

One small suggestion, Plz try to upload videos in 1080 60Fps. Thnx

My approach : select month(transaction_date) as month,sector,round(avg(revenue),0) as avg_revenue from ey_transactions t left join ey_sectors s on t.company_id=s.company_id where year(transaction_date)=2020 group by 1,2;

My approach with cte as (select year(transaction_date) as year,user_id,transaction_amount from transactions where year(transaction_date) between 2018 and 2022) select year,user_id,round(avg(transaction_amount),0) as average_amount from cte group by year,user_id;

Good work. Thumbnail looks impressive

with cte as( select transactions.*,YEAR(transaction_date) as year1 FROM transactions where YEAR(transaction_date) BETWEEN 2018 and 2022 ),cte1 as( select user_id,year1,ROUND(AVG(transaction_amount),0) as avg_transaction_amount FROM cte GROUP BY user_id,year1 ORDER BY year1 ) select year1,user_id,avg_transaction_amount FROM cte1;

same result with this query select user_id , year(transaction_date), round(avg(transaction_amount) over(partition by year(transaction_date) , user_id order by transaction_amount),2) as avg_trns from transactions

select m.department, m.name manager, count(e.id) no_of_emp from employee04 e inner join employee04 m on e.managerid=m.id group by 1,2 having no_of_emp >5 order by no_of_emp desc;

with cte as( select *,DATEDIFF(MINUTE,order_time,deliver_time) as actual_time from order_details) select del_partner,count(orderid) as delayed_orders from cte where actual_time>predicted_time group by del_partner

data={"ID":[101,102,np.nan,104,105,106], "Age":[25,np.nan,35,42,np.nan,28], "Income":[55000,63000,np.nan,72000,48000,np.nan], "City":["New York","Los Angeles","Chicago",np.nan,"San Francisco","Houston"], "Score":[88,np.nan,92,85,np.nan,91]} df=pd.DataFrame(data) For practice go here

Mam Please bring project on how sql used by data analyst in organization please mam.

select del_partner, count(*) from order_details where timestampdiff(minute, order_time, deliver_time) > predicted_time group by del_partner ;

It will be very helpful if you come up with an SQL project that can be showcased in resume for a data analyst

Could you please make a video on SQL case study (Project) - like Data Cleaning and transformation, finding insight.

Thank you for this. Can you also talk on case studies for data analyst/scientist interview

1st view

Simple, clear and to the point explanation

select e2.name, e1.department, count(*) as direct_report from employee e1 join employee e2 on e1.department = e2.department and e1.managerid = e2.id where e1.department in (select department from employee group by department having count(*) >= 10) group by 1, 2 having count(*) >= 5;

can i get the query to create the 2 tables

WITH monthly_sales_tbl AS (SELECT DATE_FORMAT(Order_Date,'%Y-%m') AS Month, SUM(Amount) AS Monthly_Sales FROM Orders GROUP BY Month) SELECT Month, Monthly_Sales, ROUND(AVG(Monthly_Sales) OVER(ORDER BY Month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ),2) AS Moving_Avg_Sales FROM monthly_sales_tbl;

with cte as ( select e2.name, e1.department, count(e1.id) as direct_reports_count from employee e1 join employee e2 on e1.managerId = e2.id and e1.department = e2.department group by 1,2 having count(e1.id)>=5 ) select c.name,c.department,c.direct_reports_count from cte c join employee e on c.department = e.department group by e.department having count(e.department)>10