Very complex approach in the video. Here is the most simplest approach (MySQL) : - SELECT customer_id, customer_name, ROUND(SUM(billed_amount) / (3 - COUNT(DISTINCT YEAR(billing_creation_date)) + COUNT(*)),1) AS avg_billed_amount FROM billing WHERE YEAR(billing_creation_date) BETWEEN 2019 and 2021 GROUP BY 1, 2

A little browsing, we can use recursive cte to generate date rows between start and end, And we just do right/left join from main table and the generated rows with year(tbl1.date) and year(tbl2.date) From that join, we will get result null if there's no trx for that user in that year Last, we just group by year(date) and customer_id, and select sum(trx amount)/count(trx amount)

Why everyone is over complicating this? Check this simple solution : It could be done with where clause to filter years instead of writing all these rows for each year, also the divisor amount could considered when we turn null values into 0: Select customer_id, customer_name, Avg(case when billing is null then 0 else billing) as billing, From table Where Extract ( year from date_column ) in (“2019”, “2020”) Group by customer_id, customer_name I wrote this from my phone so iam sorry if it is not clear, i use this code in plSql could be there are some differences such as extract year but iam sure there is an equivalent in sql Thanks

Great video explanation and walk through. At the very end, I recommend doing a final step by step summary of the entire formula just to reiterate exactly what is going on and then pausing. Thanks for everything that you do!

Nice question and a great explanation. Thanks a lot. I also tried my own and used string functions to come up with the solution. with cte1 as ( select customer_id, customer_name, count(*) as ct , sum(billed_amount) as amount, string_agg(cast(extract (year from billing_creation_date) as varchar), ',' ) as all_year from billing where extract(year from billing_creation_date) in (2019,2020,2021) group by 1,2 ) , cte2 as ( select *, case when all_year not like '%2019%' then 1 else 0 end as is_2019, case when all_year not like '%2020%' then 1 else 0 end as is_2020, case when all_year not like '%2021%' then 1 else 0 end as is_2021 from cte1 ) select customer_id, customer_name, round(amount*1.0/(ct+is_2019 + is_2020+is_2021),2)as avg from cte2

Nice!! I’ve never seen someone explain SQL problems for interviews and gotta admit I love the format and the way you explain it. Insta-subscribed🎉. I’ve been practicing in codewars but I always feel like they’re either too simple tasks asking for a JOIN or something I just have never thought before. This really helped me improving my problem solving skills and the way I tackle SQL problems

select customer_name, sum(billing_amount)/(count(*) - count(distinct year(billing_creation_date)) +3) AS AVG_Billed_amount from table1 where year(billing_creation_date) BETWEEN '2019' and '2021' group by customer_name;

An alternative solution to this query without using case statement with cte as (select customer_name,EXTRACT(Year from billing_creation_date) as billing_year,billed_amount from billing where EXTRACT(Year from billing_creation_date)>=2019), table1 as (select customer_name,billing_year,billed_amount,(select count(distinct billing_year) as cnt2 from cte t1 where t1.customer_name=t2.customer_name group by customer_name ),count(*) over(partition by customer_name) as cnt1 from cte t2 order by customer_name,billing_year), table2 as (select customer_name,billing_year,billed_amount,(cnt1+(3-cnt2)) as cnt3 from table1), table3 as (select distinct customer_name,sum(billed_amount) over(partition by customer_name) as s1,cnt3 from table2) (select customer_name,round(s1::decimal/cnt3,2) as avg_val from table3)

I would be asking the interviewer if they wanted the average transaction billing for a customer for all transactions between 2019 to 2021 or if they wanted the average for each customer for each year (2019,2020,2021). Throwing in a transaction for 0 just because the customer did not have any other transactions that year makes no sense.

The answer and approach is nice and this is good solution, I admire that. However, had I been interviewer then would have asked now can you write the same for between year 1990 - 2022? Would you write case statement for those 32 years? The approach should have been that the query will work for dynamic input..... PS : I am also trying to think of a dynamic solution.

Thank to your lessons I was able to solve this query with joins in MySQL with cte as (select distinct b1.customer_id,b1.customer_name, year from billing b1 cross join (select 2019 year union select 2020 union select 2021) k) select cte.customer_id, cte.customer_name,concat(round(avg(coalesce(b.billed_amount,0)),2),'$') av_billing_amount from cte left join billing b on b.customer_id=cte.customer_id and year(b.billing_creation_date)=cte.year group by cte.customer_id,cte.customer_name;

Great Explanation ! but if date range increases in will be difficult using case . Here I have dynamic Query WITH integer_sequence(n) AS ( SELECT 2019 -- starting value UNION ALL SELECT n+1 FROM integer_sequence WHERE n < 2021 -- ending value ) Select customer_id,customer_name,AVG(Amount) from ( Select A.n as bill_Year,A.customer_id ,A.customer_name,ISNULL(Amount,0) AS Amount from ( SELECT * FROM integer_sequence A cross join (Select distinct customer_id,customer_name from Test_SQL)B ) A left outer join Test_SQL B on A.n=DATEPART(YEAR,B.ddate) and A.customer_id=B.customer_id ) B group by customer_id,customer_name

I always learn so much from you. You have a way of explaining things to so that it makes sense. Great job. As a person who is read only access and cannot create tables, these CTE statements are always so helpful!

Superb explanation...even a guy who doesnt even hear about SQL can understand from your video....great

Great video! Thank you for the insightful explanation. I applied a Nested CASE WHEN approach to solve this problem, and it produced the same result as shown in the video. Here's the query I used: SELECT customer_id, customer_name, (SUM(CASE WHEN strftime('%Y', billing_creation_date) IN ('2019','2020','2021') THEN billing_amount END) / (CASE WHEN SUM(CASE WHEN strftime('%Y', billing_creation_date) = '2019' THEN 1 ELSE 0 END) = 0 THEN 1 ELSE SUM(CASE WHEN strftime('%Y', billing_creation_date) = '2019' THEN 1 ELSE 0 END) END + CASE WHEN SUM(CASE WHEN strftime('%Y', billing_creation_date) = '2020' THEN 1 ELSE 0 END) = 0 THEN 1 ELSE SUM(CASE WHEN strftime('%Y', billing_creation_date) = '2020' THEN 1 ELSE 0 END) END + CASE WHEN SUM(CASE WHEN strftime('%Y', billing_creation_date) = '2021' THEN 1 ELSE 0 END) = 0 THEN 1 ELSE SUM(CASE WHEN strftime('%Y', billing_creation_date) = '2021' THEN 1 ELSE 0 END) END )) AS avg_billing_amount FROM billing GROUP BY customer_id, customer_name;

The way u explain is simply awesome.. initially I thought it must be very complex but after ur explanation it looks simple…

For the first condition A they have mentioned billing amount should be taken for a year then the total billing amount for 2020 would be 100+150$=250$ and then we have to take the average which would be 0+250+100=350/3=116.66 rather than dividing it by 4. I think this is also a typo error.

Hi Taufiq, First of all thank you for your service. You are doing a wonderful job for citizen data analyst like me. Hence I am sharing my code for review. SQL flavor - PostgreSQL. with year as ( Select extract(year from billing_creation_date) as year from billing where extract(year from billing_creation_date) is between '2019' and '2021' ) Select b.customer_id as customer_id, b.customer_name as cust_name, avg(coalesce(b.billing_amount,0)) as avg_bill_amt from year y left join billing b on y.year = extract(year from b.billing_creation_date) group by y.year

I create rows not columns. Not as sophisticated as some below but here is my version with a cross join and each step in a subquery. Yeah too long, but fun :) with bill_qry as ( select customer_id, customer_name, year(billing_creation_date) as bill_year, convert( decimal (10,2) , sum(billing_amount) ) as bill_sum , convert( decimal (10,2) , count(billing_id) ) as bill_cnt from billing where year(billing_creation_date) > 2018 -- should be a prompt group by customer_id, customer_name, year(billing_creation_date) ), d_year as ( select min(bill_year) as min_year, max(bill_year) as max_year -- one row from bill_qry ), y_range as ( select n = 0, (min_year) as t_year --year 1 from d_year union all select n+1, (min_year) as t_year --year 1 from y_range, --recursive query d_year -- one row where n < max_year - min_year -- can't be over 100 rows ), year_table as ( select t_year + n as t_year from y_range --use this table to cross join the data table ), bill_qry2 as ( select t_year , customer_id, customer_name ,case when t_year = bill_year then bill_sum else 0 end as bill_sum2 --create rows instead of columns ,case when t_year = bill_year then bill_cnt else 0 end as bill_cnt2 --create rows instead of columns from year_table cross join bill_qry ), bill_qry3 as ( select t_year, customer_id, customer_name, sum(bill_sum2) as bill_sum3, --years with no entry reduced to one row, zero amount, zero instance sum(bill_cnt2) as bill_cnt3 --years with no entry reduced to one row, zero amount, zero instance from bill_qry2 group by t_year, customer_id, customer_name ), bill_qry4 as ( select customer_id, customer_name, bill_sum3 as bill_sum4, case when bill_cnt3 = 0 then 1 else bill_cnt3 end as bill_cnt4 from bill_qry3 ) --final query select customer_id, customer_name, convert ( decimal (10,2), ( sum(bill_sum4) / sum(bill_cnt4) ) ) as avg_pay_final from bill_qry4 group by customer_id, customer_name

very interesting

I was able to solve before watching solution. Used a different method using row_number() to count additional payments in one year. Assumed every groups sum should be divided by 3 plus any additional payments. Basically just get the sum of all payments for group and then decide what to divide by. -- my solution select customer_id,customer_name, concat('$',cast(total_sales / cast(3 + additional_payments as decimal(5,2)) as decimal(5,2))) as avg_billing_amount from ( select distinct customer_id, customer_name, total_sales, max(additional_payments) over (partition by customer_name) - 1 as additional_payments from ( select distinct customer_id,customer_name, sum(billed_amount) over (partition by customer_name ) as total_sales, max(num_billing) over (partition by customer_name, Y order by y) as additional_payments from ( select customer_id,customer_name, billed_amount, y, ROW_NUMBER() over (partition by customer_name, Y order by y) as num_billing from ( select *, year(billing_creation_date) as y from billing where billing_creation_date > '2019-01-01') X) z) q) n

Sir your the dictionary for my SQL practice, Thanks for the video..!!😀😀

Hi Toufiq , good problem statement and video. My Soluition for the problemt . %sql WITH CTE AS( select distinct customer_id,customer_name,Year from Billing join( select '2019' as Year UNION select '2020' as Year UNION select '2021' as Year ) Y ) select C.customer_id,C.customer_name, Round(AVG(coalesce(B.billed_amount,0)),2) as avg_billing_amount from CTE C left join billing B ON C.customer_id=B.customer_id AND C.customer_name=B.customer_name AND C.Year=EXTRACT(year from B.billing_creation_date) group by C.customer_id,C.customer_name order by 1,2

I create rows not columns. 😃 I used SQLLITE drop table billing; create table billing ( customer_id int , customer_name varchar(1) , billing_id varchar(5) , billing_creation_date DATE , billed_amount int ); insert into billing values (1, 'A', 'id1', DATE('2020-10-10'), 100); insert into billing values (1, 'A', 'id2', DATE('2020-11-11'), 150); insert into billing values (1, 'A', 'id3', DATE('2021-11-12'), 100); insert into billing values (2, 'B', 'id4', DATE('2019-11-10'), 150); insert into billing values (2, 'B', 'id5', DATE('2020-11-11'), 200); insert into billing values (2, 'B', 'id6', DATE('2021-11-12'), 250); insert into billing values (3, 'C', 'id7', DATE('2018-01-01'), 100); insert into billing values (3, 'C', 'id8', DATE('2019-01-05'), 250); insert into billing values (3, 'C', 'id9', DATE('2021-01-06'), 300); select * from billing; -- Soltion ❤ with billingTable as ( select customer_id, customer_name, strftime('%Y',billing_creation_date) as year, billed_amount from billing where year in ('2019', '2020', '2021') ), missingYears as ( select customer_id, customer_name, 3 - count(distinct year) as missingYears from billingTable group by customer_id, customer_name ), sumBilling as ( select customer_id, customer_name, sum(billed_amount) as amount_sum, count(year) as countYears from billingTable group by customer_id, customer_name ), result as ( select missingYears.customer_id, missingYears.customer_name, sumBilling.amount_sum, (sumBilling.countYears + missingYears.missingYears) as countYears from missingYears inner join sumBilling on missingYears.customer_id = sumBilling.customer_id and missingYears.customer_name = sumBilling.customer_name ) select customer_id, customer_name, ROUND(amount_sum*1.0 / countYears, 2)||'$' as avg_billed_amount from result

Good narration dear. I tried this approach and it seems to be fine. select distinct year(#billing_creation_date) as yr into #yearmaster from #billing where year(#billing_creation_date) between 2019 and 2021; select distinct customer_name as customer_name into #custmaster from #billing where year(#billing_creation_date) between 2019 and 2021; select * into #masterrecord from #yearmaster ym cross join #custmaster cm order by ym.yr,cm.customer_name select ym.customer_name,AVG(convert(money,isnull(bl.billed_amount,0))) as average from #masterrecord ym left join #billing bl on (year(#billing_creation_date) = ym.yr AND ym.customer_name = bl.customer_name) group by ym.customer_name order by ym.customer_name

with prasad as ( select *, extract (year from billing_creation_date) as years from billing where billing_creation_date between '2019-01-01' and '2021-12-31' ) select customer_id, sum(billed_amount)/ (case when count(distinct years)=1 then count(years)+2 when count(distinct years)=2 then count(years)+1 else count(years) end) as billed_amount from prasad group by 1;

That was wild. A bunch of lights came on as I have been studying simple SQL terminology and basic query s cool thx

I find the solution you provided to be quite complex, inefficient and a bit too specific. Here's my solution: Table Definition: Billing ( cid int, cname varchar(10), bid varchar(10) primary key, bdate date, bamount float ) Query: SELECT b.cid, b.cname, SUM(b.bamount) / (COUNT(*) + 3 - COUNT(DISTINCT(YEAR(bdate)) AS average FROM billing b WHERE YEAR(b.bdate) BETWEEN 2019 AND 2021 GROUP BY b.cid; Explanation: Instead of having so many cases, we can add together the total no of billings per customer + the no of years that don't have billings (3 - COUNT(DISTINCT(YEAR(bdate)).Moreover, before grouping by customer, we filter using "WHERE YEAR(b.bdate) BETWEEN 2019 AND 2021". Hope it's useful! :))

I learned something new .the way you explained it cleared my concept thank you so much .

We can create a cte with their customer name and number of time it comes in between 2019 to 2021 and then we can find the sum of the values for the years between 2019 and 2021 and then divide it by the count values which we get from cte and group by the customer names

How about this? select customer_id, customer_name, concat(round((sum(billing_amount)/(count(distinct billing_id) - count(distinct year(billing_creation_date)) + 3)),2),"$") as avg_bill_amt from data where bill_date between '2019-01-01' and '2021-12-31' group by 1,2;

I would resolve it like that: with all_year as (select 2019 as year from dual union select 2020 as year from dual union select 2021 as year from dual), all_ids_sal as( select distinct id, b.year, case when b.year in (select year from billing c where a.id=c.id) then (select salary from billing d where d.id=a.id and d.year=b.year) Else 0 end as salary from billing a, all_year b) select id, round(avg(salary)) from all_ids_sal group by id

this works for me: SELECT customer_id, customer_name, sum(billing_amount) AS total_billing_amt, count(billing_id) as billing_cnt, 3-count(distinct year) as missing_billing_cnt, sum(billing_amount)/(count(billing_id)+3-count(distinct year)) as avg FROM (select * from df where year >= 2019 and year

Sir actually the output for B is 150 as given in question and not 200 as you said. In 2021 there is no transaction for B so it will be considered as 0. Final avg will be 600/4 = 150

A lot of code. You can do select customer_name, customer_id, sum(billed_amount)/3 where year(billing_creation_date) between 2019 and 2021 group by customer_name, customer_id

Thank you for this! More concept understanding of every step you are taking and thinking

Following is my dynamic solution to the problem: With c_tot as { Select customer_id ,customer_name ,SUM(billing_amount) over( partition by customer_id) as sm ,COUNT(billing_creation_date) over (partition by customer_id) as nbr ,COUNT(DISTINCT YEAR(billing_creation_date)) over ( partition by customer_id) as discnbr From Where YEAR(billing_creation_date) ≥2019 and YEAR(billing_creation_date) ≤2021 } Select customer_id ,customer_name. sm/(nbr+(3-discnbr)) as average_billing From c_tot P.s: (end_year - start_year)+1 =3

"SELECT customer_id, customer_name, AVG(ISNULL(avg_billing_amount,0)) FROM billing WHERE YEAR(billing_creation_date) BETWEEN 2019 AND 2021 GROUP BY customer_id, customer_name" Whats wrong with this??

Hi TFQ, thanks for sharing this problem and all your efforts!

::decimal (only this part) isn't working in microsoft sql server..whats the other way of changing this avg_bill_amount into decimal?

Please solve more interview questions like this

Why do we divide by 4 for 'A'? In the data for 'A' we see only 3 bill_id (2 in 2020 and 1 in 2021). For 2019 we set bill_amount as 0 but there is no bill_id. How it could be 4th bill_id if there was nothing to sell?

Good solution as per the problem statement. But while calculating Average billing amount for three years, we should divide the total billing amount by 3 rather than by number of bills being considered. May be the problem statement example is not accurate.

PLEASE PROVIDE THE TABLE CREATION AND DATA INSERTAION QUERIES WHEN U DO SUCH KIND OF VIDEOS.THAT WILL HELP SAVE TIME

such a wonderful video.. thanks for sharing.

Great one , enjoyed every bit of it , thanks for sharing this 🙂

Great Vid !!!! I really enjoy your way of explaining complex things so easily !!! Keep going !!

Hi bro, I learnt a lot after watching your videos, please make more videos

I think Below Query Is more Dynamic with smaller output and less character But I was Learn From Your video. declare @f_y int = 2019,@l_y int = 2021 ;with ctc as( select @f_y as y union all select y+1 as y from ctc where y

Would a better, more dynamic solution be a date scaffold? Reduce the need for lots of case statements

select customer_id,round(sum(billing_amount)/3) as avg_bills from customer_demo where customer_id = 1 or customer_id = 2 or customer_id = 3 group by customer_id; Is this querry correct?

I tried with chat gpt, but failed to bring the correct value

man i love how do you explain, thanks from argentina genio!

Journalist here. I would do that with the Help of knime software, using joiner node and math formula node, also, a little bit or rule engine node.

Thank you thoufiq for the question.

Nice video. Very clear explanation. Keep it up.

Hi Thoufiq - Great explanation. I was thinking, what if the problem statement and data set was expanded to 10 or 20 years and how would that impact the solution.

interesting, i still have a lot to learn about SQL

Interesting: classic case of overfitting with the test sample. The averages should be calculated by 3(considering 3 years), unless this is a made up scenario where they are looking for per year average and the denominator is the count of bill amounts

my approach: with c as (select * from (select *,row_number() over(partition by y,customer_id order by customer_id) rn from (select *, year(billing_creation_date) y from billing) t) t1 where rn=1 and y >= 2019 and y

Big thanks bhai, you are doing amazing job

Wonderful thoufique . It was really helpful

Wonderful explanation Toufiq 👌👌

This is a generic one... you can pass the year range as parameters. with y as ( select yr from generate_series(2019, 2021, 1) yr ), c as ( select distinct(cust_id) from bill ) select x.cust_id, sum(coalesce(y.sum_amt, 0)), avg(coalesce(y.sum_amt, 0)) from ( select yr, cust_id from c cross join y ) x left join ( select cust_id, date_part('year', bill_date) as billyr, sum(bill_amt) as sum_amt from bill group by cust_id, date_part('year', bill_date) ) y on x.cust_id = y.cust_id and x.yr = y.billyr group by x.cust_id order by x.cust_id;

Great explanation as usual, thanks for great walkthrough 💯

my approach count distinct dates WITH cte AS ( SELECT customer_id, customer_name, SUM(billed_amount) as bill, CASE WHEN COUNT(DISTINCT YEAR(billing_creation_date)) =2 THEN COUNT(billing_creation_date) + 1 WHEN COUNT(DISTINCT YEAR(billing_creation_date)) =1 THEN COUNT(billing_creation_date) + 2 ELSE COUNT(billing_creation_date) END AS count_imp FROM billing WHERE billing_creation_date >= '2019-01-01' AND billing_creation_date

Thank you sir. How do we approach it if we have about 50 years worth of data and obviously we don't want to write CASE statement for each year.

Easy .. SELECT customer_name, SUM(billing_amount) / (COUNT(*) - COUNT(DISTINCT YEAR(billing_creation_date)) + (YEAR('2021') - YEAR('2019') + 1)) AS AVG_Billed_amount FROM table WHERE YEAR(billing_creation_date) BETWEEN '2019' AND '2021' GROUP BY customer_name; ``` In this modified query, `(YEAR('2021') - YEAR('2019') + 1)` calculates the difference in years between 2019 and 2021 (inclusive), which is then added to the denominator of your formula. This way, you're accounting for the dynamic year range without hardcoding the value "3".

easier solution- select customername,customerid, case when max(dr) = 3 then round(sum(billing_amount)/count(customerid),2) when max(dr) = 2 then round(sum(billing_Amount)/(count(customerid)+1),2) when max(dr) = 1 then round(sum(billing_Amount)/(count(customerid)+2),2) end as dlsf from(select *,dense_rank() over(partition by customerid,customername order by billing_cd) as dr from averagee where year(billing_cd) in('2019','2020','2021')) group by customerid,customername

Is it only me or no one else is noticing that the calculations in the task do not match? the amount for B is 600/3 = 200 not 150. the first row must be divided by 3 not 4 - there are only 3 years in the interval not 4 . C is the only right answer.

Great practice

How many case statements we would write if we r asked to find average from 2001 to 2022 or even larger no of year....any other solution please ?

I'm not clear about( with cte as) why we use this and what for all the queries we use the same statement. otherwise suggest me some other way

is it not possible to setup a scan 2019 to 2021 instead of individual listings?

Excellent explanation bro...

Superb explanation 👌 👏 👍

Super brooo...u rocked it maaaaaannnnn really u are SQL god

I couldn't download the dataset just the SQL script available for download.

Great work. Lots of fun. 👍🏽

My Query with bill_summary as (select customer_id, customer_name, sum(billed_amount) as total_bill, count(customer_name) as bill_count, count(distinct(extract(year from billing_creation_date))) as distinct_years from billing where extract(year from billing_creation_date)>='2019' group by customer_id, customer_name) select customer_id, customer_name, round(total_bill/(bill_count+3-distinct_years),2) as average from bill_summary

You nailed it.

🙂Very Nice Explanation.

Keep it up 😃 I just love the way you explain minute things about sql ....thank you for not making me think that sql is complex 😇

hello Thoufiq, assume you're working on a huge data, is it gonna be the perfect solution to provide? second why didn't you use the avg function with a where clause testing on the extracted year from billing_ date which should be between 2019 and 2021 ? I mean this way : with billing_2019 as ( select customer_id,customer_name, extract(year from billing_creation_date) as billing_year, billed_amount as billing_amount from p_work.billing) select customer_id,customer_name,round(avg(billing_amount),2) as billed_amount from billing_2019 where billing_year between 2019 and 2021 group by 1,2 order by 1 asc ;

Hi Thoufiq! Great way to explain. Thank you. Please let me know if you can help with solving any particular interview query that is related to Day 1 retention rate for users. Really appreciate it.

If this were a business problem for a real company, it seems like this solution leaves out some assumptions: What if customers weren't in the database for the entire period? What if there were outlier billing amounts? Could SQL IF be used to tighten up some of this variability?

can we use select *, avg(billing amount) from table name where date between 2019 and 2021 group by custmer name ;

with total as (select customer_id, customer_name, date_trunc(billing_creation_date,year) as year, sum(billing_amount) as sum_billing from billing where year between '01-01-2019' and '01-01-2021' group by customer_id) ,final as (select customer_id,customer_name,sum_billing/count(distinct year) as avg_billing_amount) from total ) select * from final Wrote this solution down in notepad but didn't get to test it.

Why isn’t it Select customer_id, customer_name, avg(billing_amount) as name_avg_biling_amount From table_name Group by customer_name Where billing_creation_date between 01-01-2019 and 12-31-2021

Hi @techTFQ. In the above video,I have a doubt. Here the date range is only from 2019 to 2021. Suppose we have a data range from 2009 to 2020. I would like to know if this solution works if we have the above range? Please let me know.

Why not: SELECT customer_id, Customer_name, AVG(billed_amount) as average FROM billing WHERE billing_creation_date BETWEEN ‘2019-01-01’ AND ‘2021-31’ GROUP BY 1,2 ORDER BY 1; ?

Can we do this without case statement by just using.."between date1 and date2" along with where condition, group by ??

/* The billing_creation_date column is not how the video shows and I did not format the number at the end to be a decimal */ CREATE TABLE billing ( customer_id INTEGER, customer_name TEXT, billing_id TEXT, billing_creation_date INTEGER, billing_amount INTEGER); INSERT INTO billing (customer_id, customer_name, billing_id, billing_creation_date, billing_amount) VALUES (1 , 'A' , 'id1' , 2020, 100), (1 , 'A' , 'id2' , 2021, 150), (1 , 'A' , 'id3' , 2021, 100), (2 , 'B' , 'id4' , 2019, 150), (2 , 'B' , 'id5' , 2020, 200), (2 , 'B' , 'id6' , 2021, 250), (3 , 'C' , 'id7' , 2018, 100), (3 , 'C' , 'id8' , 2019, 250), (3 , 'C' , 'id9' , 2021, 300); /* ANSWER */ SELECT customer_id, customer_name, /* boolean matrix multiplied by the billing amounts */ ROUND( (SUM((billing_creation_date='2019')*(billing_amount))+ SUM((billing_creation_date='2020')*(billing_amount))+ SUM((billing_creation_date='2021')*(billing_amount)))/ /* Divided by the sum of a Conditional column wrapped in a Case statement */ (CASE WHEN SUM(billing_creation_date = '2019') = 0 THEN 1 ELSE SUM(billing_creation_date = '2019') END + CASE WHEN SUM(billing_creation_date = '2020') = 0 THEN 1 ELSE SUM(billing_creation_date = '2020') END + CASE WHEN SUM(billing_creation_date = '2021') = 0 THEN 1 ELSE SUM(billing_creation_date = '2021') END),2) AS 'avg_billing_amount' /* Finally grouped by ID and name */ FROM billing GROUP BY customer_id, customer_name;

Fun challenge! My take on it: WITH cte AS ( SELECT DISTINCT YEAR(b1.billing_creation_date) AS 'year' , b2.customer_id , b2.customer_name , COALESCE(b3.billed_amount,0) AS billing_amount FROM billing b1 LEFT JOIN billing b2 ON 1=1 LEFT JOIN billing b3 ON b2.customer_id = b3.customer_id AND YEAR(b1.billing_creation_date) = YEAR(b3.billing_creation_date) WHERE YEAR(b1.billing_creation_date) IN (2019,2020,2021) ) SELECT customer_id , customer_name , AVG(billing_amount) AS avg_billing_amount FROM cte GROUP BY 1,2 ORDER BY 1,2

Why we weren't change null to 1?,it can be show 1 instead of null value?

select id, name, sum(amount)/sum(counter) as avg_billed from ( select customer_id as id, customer_name as name, billing_amount as amount, if amount>0 then 1 else 0 end if as counter where billing_creation_date between '2019-01-01' and '2021-12-31' ) A group by id,name;

Hi, thanks for video. I believe your solution does not scale well. Imagine there are more than 3 years to take. In this particular case it's better to generate a "table" with all combinations of customers and billing years and join it with the actual data. Here is an example for postgresql: with users as (select distinct customer_id, customer_name from billing), period as (select generate_series(2019,2021) p_year) select u.customer_id, u.customer_name, avg(coalesce(b.billing_amount,0)) avg_billing_amount from users u cross join period p left join billing b on b.customer_id = u.customer_id and p.p_year = date_part('year', b.billing_creation_date)::int group by u.customer_id, u.customer_name

what if we are asked to cacluate last 20 years like this ? is there any recursive way of doing this ?

How are we getting count as 3 for all the cells

Thanks for explanation ☺