COVID Cases - SQL Interview Query 16 | SQL Problem Level "MEDIUM"

[@florincopaci6821]

My approach in Sql Server with one select: SELECT month(dates)as month, round(cast(sum(cases_reported)as float) / sum(sum(cases_reported))over(order by month(dates) rows between unbounded preceding and 1 preceding)*100,1) as percentage_increase FROM covid_cases group by month(dates) Hope it helps

[@gphanisrinivasful]

Fantastic use of rows between clause!

[@malcorub]

This is possibly the most practical of all the problems since day one. This one did not feel like a random problem to solve. Kudos!

[@malcorub]

After stepping away from my comment, I remembered that you mentioned it was a "business analyst" problem submitted by a friend. Perhaps the other "random puzzle" type problems are geared more toward data engineers and not data and business analyst. Just sharing my thoughts here... Keep it going, we're half way there. Greetings from San Diego CA USA!!!! Tomorrow is Sunday, so go easy on us.... my head will already be hurting from tonight's fun. LOL

[@Winter_Soldier100]

Love all your videos ! The best guy out there for SQL ! Thanks for all your efforts ! If possible please start a Power BI series

[@TheCraigie007]

Thanks for another great challenge. My Solution: ; WITH Selected_Months AS ( SELECT DATEPART(MONTH , case_date) AS Month_Num, SUM(case_number) AS Reported_Cases FROM covid_cases GROUP BY DATEPART(MONTH , case_date) ), Running_Reported_Cases AS ( SELECT *, SUM(Reported_Cases) OVER(ORDER BY Month_Num) AS Total_Cases FROM Selected_Months ) SELECT *, CASE WHEN Month_Num > 1 THEN CAST(CONCAT(CAST((Reported_Cases * 1.0 / LAG(Total_Cases, 1) OVER(ORDER BY Month_Num) * 100) AS decimal(10,1)),'%') AS varchar) ELSE '-' END AS Percentage_Increase FROM Running_Reported_Cases

[@user-fx9gf2eb2s]

This is amazing SQL queries tasks, covering each topics very well. It increases the confidence & interest while solving & practicing it. Such a great work done by @techTFQ. God bless you always. Thanks for such informative sessions.

[@Alexpudow]

ms sql dataset create table covid_cases ( cases_reported int, dates date ); insert into covid_cases values(20124,'2020-01-10'); insert into covid_cases values(40133,'2020-01-15'); insert into covid_cases values(65005,'2020-01-20'); insert into covid_cases values(30005,'2020-02-08'); insert into covid_cases values(35015,'2020-02-19'); insert into covid_cases values(15015,'2020-03-03'); insert into covid_cases values(35035,'2020-03-10'); insert into covid_cases values(49099,'2020-03-14'); insert into covid_cases values(84045,'2020-03-20'); insert into covid_cases values(100106,'2020-03-31'); insert into covid_cases values(17015,'2020-04-04'); insert into covid_cases values(36035,'2020-04-11'); insert into covid_cases values(50099,'2020-04-13'); insert into covid_cases values(87045,'2020-04-22'); insert into covid_cases values(101101,'2020-04-30'); insert into covid_cases values(40015,'2020-05-01'); insert into covid_cases values(54035,'2020-05-09'); insert into covid_cases values(71099,'2020-05-14'); insert into covid_cases values(82045,'2020-05-21'); insert into covid_cases values(90103,'2020-05-25'); insert into covid_cases values(99103,'2020-05-31'); insert into covid_cases values(11015,'2020-06-03'); insert into covid_cases values(28035,'2020-06-10'); insert into covid_cases values(38099,'2020-06-14'); insert into covid_cases values(45045,'2020-06-20'); insert into covid_cases values(36033,'2020-07-09'); insert into covid_cases values(40011,'2020-07-23'); insert into covid_cases values(25001,'2020-08-12'); insert into covid_cases values(29990,'2020-08-26'); insert into covid_cases values(20112,'2020-09-04'); insert into covid_cases values(43991,'2020-09-18'); insert into covid_cases values(51002,'2020-09-29'); insert into covid_cases values(26587,'2020-10-25'); insert into covid_cases values(11000,'2020-11-07'); insert into covid_cases values(35002,'2020-11-16'); insert into covid_cases values(56010,'2020-11-28'); insert into covid_cases values(15099,'2020-12-02'); insert into covid_cases values(38042,'2020-12-11'); insert into covid_cases values(73030,'2020-12-26');

[@rajathratnakaran7893]

Thank you

[@rohitjain482]

MY SOLUTION with cte as (select month(dates) as month , sum(cases_reported) as monthly_cases from covid_cases group by 1), cte2 as (select * , sum(monthly_cases) over (order by month) as total_till_month from cte) select month ,round(monthly_cases / lag(total_till_month) over(order by month) * 100 , 2) from cte2;

[@shivinmehta7368]

with cte as ( select extract(month from dates) as mth ,sum(cases_reported) as cases from covid_cases group by 1 order by 1 ) select mth,round(cases*100.00/lag(csum) over(order by mth),1) as pct from ( select mth,cases,sum(cases) over (order by mth) as csum from cte )x

[@vinothkumars7421]

Great one. Thanka a bunch

[@Cherupakstmt]

These question are really helping

[@rahultalwar4847]

MYSQL Sol: with cte as ( select substring(dates,6,2) as month,sum(cases_reported) as cases from covid_cases group by month ), cte2 as ( select month, cases, sum(cases) over(order by month) as total from cte ) select month, coalesce(round(cases/lag(total) over(order by month)*100,1),"-") as prev from cte2;

[@montudeb8060]

My solution is MS SQL server: with cte as ( select distinct DATEPART(MONTH,dates) as month_num, sum(cases_reported) over(order by DATEPART(YEAR,dates),DATEPART(MONTH,dates)) as running_sum from covid_cases ), final_cte as ( select month_num as month, round(cast(((1.0*running_sum / LAG(running_sum,1,running_sum) over(order by month_num)) - 1)* 100 as decimal(10,1)),1) as percentage_increase from cte ) select month, case when percentage_increase = 0 then '-' else CAST(percentage_increase as varchar(50)) end as percentage_increase from final_cte

[@hoperight5280]

there was a better and quicker solution then the second one but somehow couldn't figure a way out to go around it. Probably was about preciding unbounded thing , if you know it please leave your comment, would be appreciate it. --First one: select extract(month from dates) as month , sum(cases_reported) as total_cases , round(sum(cases_reported) / sum(sum(cases_reported)) over(order by extract(month from dates) )*100 ,1) from covid_cases group by extract(month from dates) order by 1 --Second one: with cte as ( select extract(month from dates) as month , sum(cases_reported) as total_cases from covid_cases group by extract(month from dates) order by 1 ) , cte2 as ( select *, sum(total_cases) over(order by month) as cum_cas from cte) select *, round(total_cases / lag(cum_cas) over(order by month) * 100,1) from cte2

[@rishithakatakam4091]

Loved the way you explains❤

[@sanjeetsignh]

SQL Server solution... with cte as ( select datepart(month, dates) as month, sum(cases_reported) as total from covid_cases group by datepart(month, dates) ) select month , case when month > 1 then cast(cast(100.0 * total / (sum(total) over wnd - total) as decimal(5, 1)) as varchar) else '-' end as percentage_increase from cte window wnd as (order by month asc rows between unbounded preceding and current row)

[@CebuProvince]

yes, thats it, very important the " between unbounded preceding and current row)" clause and shown with the Percentage Sign with cte as ( select MONTH( dates) as month, sum(cases_reported) as total from covid_cases group by month( dates) ) select month , case when month > 1 then FORMAT (100.0 * total / (sum(total) over wnd - total)/ 100,'P') else '-' end as percentage_increase from cte window wnd as (order by month asc rows between unbounded preceding and current row) I'm waiting for #17 have a nice sunday...

[@satyamsinha5429]

Thanks !

[@satyamsinha5429]

Datepart was helpful !

[@Anishcko13]

👍, if possible also do a playlist on power bi

[@mohammadshahbaz3287]

here's my solution - with base as( select a.* , case when b.friend1 in(a.friend1, a.friend2 ) then NULL else b.friend1 end as B_friend1 , case when b.friend2 in(a.friend1, a.friend2 ) then NULL else b.friend2 end as B_friend2 , coalesce(B_friend1, B_friend2) as frnd2 from Friends a left join Friends b on a.friend1 = b.friend1 or a.friend1 = b.friend2 or a.friend2 = b.friend1 or a.friend2 = b.friend2 ), base2 as( select friend1, friend2, frnd2, count(*) as cnt from base group by 1,2,3 ) select friend1, friend2, sum(case when cnt>1 then 1 else 0 end) as frnf_cnt from base2 where frnd2 is not null group by friend1, friend2 ;

[@user-mq3st9cl9j]

Thank You Sir

[@rohithb65]

with cte as (select date_part('month', dates) as month,sum(cases_reported) as total from covid_cases group by date_part('month', dates) order by month), cte1 as( select *, sum(total) over(order by month) as sumd from cte), cte2 as (select *, lag(sumd,1) over(order by month) as lgs from cte1) select month,round(coalesce( ((total/lgs) * 100),0),1) from cte2

[@user-gq6cg3ls7f]

with cte as( select MONTH(dates) as Months, SUM(cases_reported) as monthly_cases_reported from covid_cases c group by MONTH(dates) ), pct as( select *, sum(monthly_cases_reported) over (order by months) running_total from cte ) select *, round((cast(running_total as float) - lag(running_total) over (order by months)) / lag(running_total) over (order by months) * 100, 1) as pct from pct

[@sapnasaini851]

MySQL Solution - with cte as ( select cast(date_format(dates, "%c") as UNSIGNED) as mon, sum(cases_reported) monthly_cases from covid_cases group by cast(date_format(dates, "%c") as UNSIGNED) order by mon ), cte2 as ( select mon, monthly_cases , sum(monthly_cases) over(order by mon) as total_cases from cte ), cte3 as ( select mon , monthly_cases, total_cases, lag(total_cases) over(order by mon) as pre_mon from cte2 ) select mon, coalesce(round((monthly_cases/pre_mon)*100,1),"_") as percentage_increse from cte3;

[@boppanakishankanna6029]

My solution- WITH cte as( SELECT month,month_number,total_cases,SUM(total_cases) OVER(order by month_number) as cumulative_cases FROM(SELECT month,month_number,SUM(cases_reported) as total_cases FROM(SELECT TO_CHAR(dates,'month') as month,extract(month from dates) as month_number,* FROM covid_cases) k GROUP BY month,month_number) k), cte2 as( SELECT *,lag(cumulative_cases)over() as previous_month_cases FROM cte) SELECT month,ROUND(total_cases*100.0/previous_month_cases,1) as percentage_increase FROM cte2;

[@ehsanshakeri620]

thank you❤

[@fathimafarahna2633]

🔥

[@SahilGupta-fr2vh]

Wow

[@Satish_____Sharma]

Here is my solution using MYSQL with cte as (SELECT extract(month from dates) as months,sum(cases_reported) as total_cases FROM covid_cases group by extract(month from dates) order by extract(month from dates)) ,cte1 as (select months,total_cases,sum(total_cases) over (order by months)as running_sum from cte) select months,coalesce(round(total_cases*100/lag(running_sum) over (order by months),2),'-') as PERCENTAGE_INCREASE from cte1

[@mrbartuss1]

WITH summary_cte AS ( SELECT DISTINCT DATE_PART('month', dates) AS month, SUM(cases_reported) OVER( ORDER BY DATE_PART('month', dates) ASC ) summary from covid_cases ) SELECT month, ROUND( CAST( summary * 100.0 / LAG(summary) OVER( ORDER BY month ASC ) AS DECIMAL ) -100, 1 ) AS prev_summary FROM summary_cte;