Видео

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

with cte as( select c.customer_id, count(distinct p.product_category) as product_purchase from customer_contracts c join products p on p.product_id = c.product_id group by c.customer_id ) select customer_id from cte where product_purchase = (select count(distinct product_category) from products)

with cte as( select *, dense_rank() over(partition by job_category order by salary desc) as rnk from employees ) select employee_id,first_name,job_category from cte where rnk = 3 order by employee_id;

Your explanation is clear

with cte as( select title,company_id from job_listings group by title,company_id having count(*) > 1 ) select count(*) as "duplicate_companies" from cte;

select seller_name from sellers where seller_id not in (select seller_id from orders where year(sale_date) = 2020);

with order_stat as( select *, timestampdiff(minute,order_time,deliver_time) as "time_diff", case when timestampdiff(minute,order_time,deliver_time) = predicted_time then "Not delayed" else "Delayed" end as "order_status" from order_details ) select del_partner, count(order_status) as "order_count" from order_stat where order_status = "Delayed" group by del_partner;

WITH CTE(SELECT *,TIMESTAMPDIFF(MINUTE,order_time,deliver_time) as del_min FROM order_details) SELECT del_partner,COUNT(*) FROM CTE WHERE predicted_time<del_min GROUP BY del_partner

SELECT user_id, spend, transaction_date FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY transaction_date) AS rn FROM transactions) subquery WHERE rn = 3;

SELECT category, product, total_spend FROM (SELECT category, product, SUM(spend) AS total_spend, DENSE_RANK() OVER(PARTITION BY category ORDER BY SUM(spend) DESC) AS rnk FROM ProductSpend GROUP BY category,product) AS ranking WHERE rnk <=2;

with cte as( select id,num,lead(num,1) over (order by id) le1,lead(num,2) over (order by id) le2 from logs ) select num consecutive_number from cte where num=le1 and num=le2 and le1=le2;

Hi, I think This is correct Approach. Let me clear WITH Monthly_Revenue AS ( SELECT s.sector, FORMAT(t.transaction_date, 'yyyy-MM') AS month_year, SUM(t.revenue) AS monthly_revenue FROM Transaction_Table t JOIN Sectors s ON t.company_id = s.company_id GROUP BY s.sector, FORMAT(t.transaction_date, 'yyyy-MM') ) SELECT sector, AVG(monthly_revenue) AS avg_monthly_revenue FROM Monthly_Revenue GROUP BY sector;

SELECT seller_name FROM sellers WHERE seller_id NOT IN (SELECT seller_id FROM orders WHERE YEAR(sale_date) = 2020);

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

Nice explanation ..keep going .

You should have mentioned other ways to insert data into a table. For example - we can also insert data into one table from another table using select statement.

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

select artist_name, dense_rank() over(order by sum(total_appearnce) desc) as artist_rank from ( select s.*, a.artist_name, count(g.song_id) as total_appearnce from songs as s join artists as a on s.artist_id = a.artist_id left join global_song_rank as g on s.song_id = g.song_id group by s.song_id, a.artist_name ) group by 1

Using window function : select * , case when order_id%2=1 then lead(item,1,item) over(order by order_id) else lag(item,1) over(order by order_id) end as new_item from orders; Using left join : with cte as ( select o.order_id as oid1, o.item as item1, o2.order_id as oid2, o2.item as item2 from orders o left join orders o2 on o2.order_id = o.order_id + 1) select oid1 as order_id, coalesce(item2,item1) as item from cte where oid1 % 2=1 union all select oid2 ,item1 from cte where oid2 % 2=0 order by order_id

So after truncating, no rollback possible.🤔

My Approach : select s.seller_name from orders o left join sellers s on o.seller_id = s.seller_id group by 1 having count(case when extract(year from sale_date) = 2020 then 1 end) = 0

My Approach : select del_partner, count(case when predicted_time < cast (EXTRACT(EPOCH FROM (deliver_time - order_time)) / 60 as int) then 1 end ) as order_count from order_details group by 1;

My approach : select customer_id from customer_contracts cc left join products p on cc.product_id = p.product_id group by 1 having count(distinct product_category) = (select count(distinct product_category) from products)

my Output as WITH monthly_sales AS ( SELECT SUM(sales) AS total_sales, MONTH(date) AS month FROM sales GROUP BY MONTH(date) ORDER BY month ASC ), previous_month AS ( SELECT total_sales, month, LAG(total_sales, 1) OVER (ORDER BY month ASC) AS prev_revenue, LAG(total_sales, 2) OVER (ORDER BY month ASC) AS PP_revenue FROM monthly_sales ) SELECT month, total_sales, (total_sales + COALESCE(prev_revenue, 0) + COALESCE(PP_revenue, 0)) / 3 AS avg_sales_last_3_months FROM previous_month ORDER BY month ASC;

postgresql: with month_table as( select *, extract(month from order_date) as month from Orders_2 ), total_sales as( select month, sum(amount) as total_sale from month_table group by month order by month ) select month, total_sale, round((avg(total_sale) over (order by month rows between 2 preceding and current row)),2) as moving_avg from total_sales

SQL SERVER : with monthly_sales as ( select datepart ( month,order_date ) as ordered_month,sum (amount) as total_amount from orders group by datepart ( month,order_date ) ) select ordered_month, total_amount, round(avg (total_amount) over ( order by ordered_month rows between 2 preceding and current row ),2) as running_sales from monthly_sales group by ordered_month, total_amount

with cte as( select order_id,total_value,ranks,lag(ranks)over(order by order_id)counts from( select order_id,total_value,lag(total_value)over(order by order_id)ranks from( select *,row_number()over(partition by month(order_date)order by order_id )dates from( select order_id,customer_id,order_date,sum(amount)over(partition by month(order_date))total_value from orders )t1)t2 where t2.dates=1)t3) select order_id,total_value,ranks,counts,( select sum(sum_value)from (values (total_value),(ranks),(counts))as x(sum_value))/ (select count(count_value)from(values(total_value),(ranks),(counts))as x(count_value)) as sm_value from cte

SELECT employee_id, first_name, job_category FROM (SELECT *, DENSE_RANK() OVER(PARTITION BY job_category ORDER BY salary DESC) as rnk FROM employees) as Ranking WHERE rnk = 3 ORDER BY employee_id;

Nice Explanation 😊

useful thank you!

SELECT MONTH(submit_date) AS month, restaurant_id, ROUND(AVG(rating),1) AS avg_rating FROM reviews GROUP BY month,restaurant_id HAVING COUNT(review_id) >= 2 ORDER BY avg_rating DESC;

SELECT sector, date_format(transaction_date, '%Y-%m') months, ROUND(AVG(revenue),2) avg_rev FROM sectors s INNER JOIN transactions as t on t.company_id = s.company_id GROUP BY 1,2;

This is my query with cte as( select SUBSTRING(Order_Date,1,7) as date_month,Amount FROM Orders ),cte1 as( select date_month,SUM(Amount) as amount1 FROM cte GROUP BY date_month ),cte2 as( select cte1.*,COALESCE(LAG(amount1)OVER(ORDER BY date_month),0) as amount2, COALESCE(LAG(amount1,2)OVER(ORDER BY date_month),0) as amount3 FROM cte1 ),cte3 as( select cte2.*,(amount1+amount2+amount3) as expected_amount FROM cte2 ) select date_month,amount1, CASE when amount2=0.00 and amount3=0.00 THEN ROUND((expected_amount/1),2) when amount2!=0.00 and amount3=0.00 THEN ROUND((expected_amount/2),2) ELSE ROUND((expected_amount/3),2) END AS moving_average FROM cte3;

create or replace table cte (department VARCHAR, employee_count INT) as select department , count(id) from employee group by department having count(id) > 10; select department , MANAGERID , count(id) from employee where department in (select cte.department from cte where cte.department = employee.department) group by department , MANAGERID having count(id) > 5; My Solution.

with cte as( select date_format(Order_Date,'%Y-%m') as month,sum(amount) as Monthly_Sales from Orders group by date_format(Order_Date,'%Y-%m')), cte2 as( select *,lag(Monthly_Sales,1) over (order by month) as prev_month_sale1, lag(Monthly_Sales,2) over (order by month) as prev_month_sale2 from cte) SELECT cte2.month,cte2.Monthly_Sales, ROUND((COALESCE(Monthly_Sales, 0) + COALESCE(prev_month_sale1, 0) + COALESCE(prev_month_sale2, 0)) / NULLIF((Monthly_Sales IS NOT NULL) + (prev_month_sale1 IS NOT NULL) + (prev_month_sale2 IS NOT NULL), 0),2) AS moving_avg_sales FROM cte2;

Thank you for the explanation. I am even more thankful for the ability to copy sample tables and input them into my SQL. At 0:58 you say that the moving average should start calculating once 3 months of data are available. At the same time Expected Output contains average adata for January and February which contradicts the condition (as there is no 3 months history for Jan and Feb). I thought we have to omit these first 2 months as they are lacking data.

select seat_id,free from ( select *, row_number() over() as row, seat_id - row_number() over() as rown from table1a where free= 1) a where rown in (1)

Oracle SQL - SELECT EXTRACT (MONTH FROM TRANSACTION_DATE) AS MONTH, SECTOR,AVG(REVENUE) AS AVG_REV FROM TRANSACTIONS T INNER JOIN SECTORS S ON T.COMPANY_ID=S.COMPANY_ID GROUP BY EXTRACT (MONTH FROM TRANSACTION_DATE),SECTOR ORDER BY MONTH,AVG_REV;