Solving SQL Interview Query for Data Analyst asked by a Product based company
ฝัง
- เผยแพร่เมื่อ 21 ก.ค. 2024
- In this video let's solve a real SQL Interview query asked for Data Analyst position by a Product Based company.
Download the dataset and scripts from below:
techtfq.com/blog/solving-sql-...
🔴 My Recommended courses 👇
✅ Learn complete SQL: learnsql.com/?ref=thoufiqmoha...
✅ Practice SQL Queries: www.stratascratch.com/?via=te...
✅ Learn Python: codebasics.io/courses/python-...
✅ Learn Power BI: codebasics.io/courses/power-b...
🔴 WATCH MORE VIDEOS HERE 👇
✅ SQL Tutorial - Basic concepts:
• SQL Tutorial - Basic c...
✅ SQL Tutorial - Intermediate concepts:
• SQL Tutorial - Interme...
✅ SQL Tutorial - Advance concepts:
• SQL Tutorial - Advance...
✅ Practice Solving Basic SQL Queries:
• Practice Solving BASIC...
✅ Practice Solving Intermediate SQL Queries:
• Practice Solving INTER...
✅ Practice Solving Complex SQL Queries:
• Practice Solving COMPL...
✅ Data Analytics Career guidance:
• Data Analytics career ...
✅ SQL Course, SQL Training Platform Recommendations:
• SQL Course / Training
✅ Python Tutorial:
• Python Tutorial
✅ Git and GitHub Tutorial:
• Git and GitHub
✅ Data Analytics Projects:
• Data Analytics Projects
THANK YOU,
Thoufiq
![THE TOYS x NONT TANONT - ดอกไม้ที่รอฝน (spring) [Official MV]](http://i.ytimg.com/vi/pPa1d5cC8M4/mqdefault.jpg)
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
Im confused by the second part of the AVG_billed_amount ( after the / ), wouldn't a simple AVG statement work since it's grouped ?
@shubhamagrawal7068 I have written the below code for this but not getting the desired output. Can you help me find the mistake please.
select customer_id,customer_name,sum(billing_amount)/(count(*)+3-count(distinct(year(x.billing_year))))
from(
select *,year(billing_creation_date) as billing_year
from billing
where year(billing_creation_date) between 2019 and 2021) x
group by customer_id,customer_name;
this is a banger answer
Perfect!
@@amadei2 No, it won't. Try to grasp the concept behind it.
Suppose you have given 3 years to evaluate for, as in our case - 2019 to 2021.
As for records- consider 1st id , it has 3 records, for 2020 twice and 2021.
No. of years to evaluate for (2019 to 2021) = 3
No. of years (in 1st record )= 3 (i.e 2020, 2020, 2021)
No. of distinct years(in 1st record) = 2 (i.e 2020, 2021)
so the formula is -
{ No. of years (to evaluate for) - No.of distinct years } + No. of years
= { 3 - Count(Distinct(years)) } + Count *
= {3-2} + 3 = 4
for 1st record i.e id = 1, name =A
Sum = 350 Count = 4
Avg = 350/4 = 87.5 .
Hoping, now it is cleared.
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)
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!
good suggestion Sean, let me consider it for future
Yet another gem from you Sir. Hats off man. Maybe just comment each step directly in the query so that the walk-through makes even more sense. Even though it is already pretty clear. Thanks again for everything you’re doing for the Data Community.
This is priceless
PS: have you ever considered putting together a comprehensive series of tutorials for UDEMY based on proficiency levels? SQL and Pyhton for Data Analysis. I’d buy them for sure if you did 😊
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
The way u explain is simply awesome.. initially I thought it must be very complex but after ur explanation it looks simple…
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
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
well written query, quite simple and well concise query.
@@KavishSrivastava thanks for the comment , I got a chance to revisist this question again, but now when i solved it again,i used different approach using the joins. sharing the recent solution below:
with cte_year as
(
SELECT 2019 AS year
UNION ALL
SELECT 2020
UNION ALL
SELECT 2021
)
,
cte_customer as
(
select
distinct customer_id
from billing
where year(billing_creation_date) in(2019,2020,2021)
)
,
cte_customer_with_year as
(
select * from cte_customer cross join cte_year
)
select
cte_customer_with_year.customer_id , round(sum(billed_amount)/count(*),2) as average_billing_amount
from cte_customer_with_year left join billing on
cte_customer_with_year.customer_id = billing.customer_id and cte_customer_with_year.year = year(billing.billing_creation_date )
group by cte_customer_with_year.customer_id
@@muditmishra9908 again a good approach which filters extra steps compare to your previous one, but the current query's output is incomplete which is missing customer_name in the result, which will eventually requires some changes in the code.
here a try I gave :
with
c as ( SELECT f.customer_id, f.customer_name ,year, billed_amount
FROM (SELECT DISTINCT customer_id, customer_name FROM billing) f
CROSS JOIN (SELECT 2019 AS year UNION SELECT 2020 AS year UNION SELECT 2021 AS year) y
LEFT JOIN billing b ON b.customer_id = f.customer_id AND YEAR(b.billing_creation_date) = y.year
ORDER BY customer_id, year, billed_amount)
SELECT customer_id, customer_name , ROUND(AVG(COALESCE(billed_amount, 0)),2) AS avg_billed_amount
from c 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!
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;
Great explanation as usual, thanks for great walkthrough 💯
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
Thank you for this! More concept understanding of every step you are taking and thinking
Hi TFQ, thanks for sharing this problem and all your efforts!
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
Great Vid !!!! I really enjoy your way of explaining complex things so easily !!! Keep going !!
Thank you Allan ☺️
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
Bhai aapke approach me wo year ko consider nhi kiya ja rha jiska record exist nhi kr rha table me between 19 and 21.
Suppose if we consider the customer A then according to table we have two records for year 20 and one record for year 21 but no record for year 19. If there would have been a record for year 19 with billingamt as null then your query may work well. But for now its of no use.
That's what makes this question tricky and lengthy.
According to your query the first output record would be:
1 A 116.666
but the expected output mentioned in ques is:
1 A 87.5
@@50_saifalikhan33hmmm i see that if we have no record of year 2019 we should add a record with 0 value. I understand now my answer could be not correct
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.
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;
That was wild. A bunch of lights came on as I have been studying simple SQL terminology and basic query s cool thx
Would a better, more dynamic solution be a date scaffold? Reduce the need for lots of case statements
such a wonderful video.. thanks for sharing.
Great work. Lots of fun. 👍🏽
Superb explanation...even a guy who doesnt even hear about SQL can understand from your video....great
Glad it helped bro
I learned something new .the way you explained it cleared my concept thank you so much .
Wonderful thoufique . It was really helpful
Sir your the dictionary for my SQL practice, Thanks for the video..!!😀😀
Glad to be helpful bro
Great one , enjoyed every bit of it , thanks for sharing this 🙂
glad to hear that
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
Nice video. Very clear explanation. Keep it up.
Wonderful explanation Toufiq 👌👌
Hi bro, I learnt a lot after watching your videos, please make more videos
Super brooo...u rocked it maaaaaannnnn really u are SQL god
Superb explanation 👌 👏 👍
Excellent explanation bro...
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 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
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
Big thanks bhai, you are doing amazing job
thanks for the constant support Ahmed ❤
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.
Either the author of the post copied the problem's details wrongly or the problem was wrongly stated in the first place.
In any case what average is wanted by the interviewer should be more clearly stated.
If only the given info were available I'd assume that you should make an average over the single year for a single customer ( E_Y = Sum[transactions_yearY]/#transactions_yearY] ) and then average over the years ( E = Sum[ E_Y1, E_Y2, ..., E_YN]/N]. With this formula customer A in the table would have an average of 75, not 87.5.
Great practice
man i love how do you explain, thanks from argentina genio!
Your welcome buddy ☺️
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 😇
Thank you 🙏🏼 glad you liked it
interesting, i still have a lot to learn about SQL
You nailed it.
Please solve more interview questions like this
Thank you thoufiq for the question.
Your welcome bro
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
🙂Very Nice Explanation.
Thank you 🙏🏼
PLEASE PROVIDE THE TABLE CREATION AND DATA INSERTAION QUERIES WHEN U DO SUCH KIND OF VIDEOS.THAT WILL HELP SAVE TIME
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
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.
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! :))
Good Explanation ❤
very interesting
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
amazing.
Thanks for explanation ☺
your welcome
Can we do this without case statement by just using.."between date1 and date2" along with where condition, group by ??
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;
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
very nice
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?
It is wrong. The average for customer A and B are incorrect in the problem.
I feel that this problem was never used in an interview, or if it was it was done very poorly.
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.
I feel the solution would work fine without much performance issue but still need to test it out with large data to be sure..
I think this solution works nicely for a small range like the one he shown, but if we were to expand it, I'd rather use nested selects in order for it to be scalable
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.
if u get the dynamic solution pls paste here, it will be helpful
For average total: 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 '1990-01-01' and '2021-12-31' ) A group by id,name;
For average per year goup by column year(billing_creation_date) as year.
For a dynamic solution you would use a date scaffold
very bad solution, outer join on sub select years (can be replace by args values) and groupe by average. More more more simple.
Can be also write by using "with as ..." for more clarity
Create a sequence of years in a cte based on year start and end. Create another cte that includes year as a column calculated from the original table. Right outer join 1st cte on to 2nd cte on year. Create a subsequent cte that groups on year and other columns of interest, aggregating measures of interest. In this case, avg(billingAmount). Boom, now you can aggregate over any year range including years with no billing.
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.
is it not possible to setup a scan 2019 to 2021 instead of individual listings?
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
can we use select *, avg(billing amount) from table name where date between 2019 and 2021 group by custmer name ;
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.
I think so too, it confusing me at first. What about it @techFTQ?
Yes, that is clearly an error. The average is per year therefore the division must be done always by 3 years, not four.
Unless the average is per billing event, then we need to ignore the cases with zero.
@@mikatu Wrong, learn english
Thank You, been looking through the comments to see if anyone else had the same query. So the amounts should be :
1 A 116.67
2 B 200.00
3 C 183.33
::decimal (only this part) isn't working in microsoft sql server..whats the other way of changing this avg_bill_amount into decimal?
Great explanation sir....👍
I have doubt
Can't we use aggregate function to sum_2019,20,21 to print average of them
Definitely. This does not require any if statements
Select customer_id, avg(Billing_amount) from table where date between 2019 and 2020
PS: PSUEDO Code
Which one u r using for execution
what if we are asked to cacluate last 20 years like this ? is there any recursive way of doing this ?
wow an easy question
Hi, I an interview I got a question, alternative commands for full outer join
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;
Sir, kindly upload Date and Time functions in SQL which are using organization
Hi Karthik, you can check a detailed video on SQL functions on my TH-cam channel. here is the link: th-cam.com/video/L048LM88iY4/w-d-xo.htmlsi=5XYsHnEycSECwfoy
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
Hi bro I 2ant to learn Oracle SQL ...can i get full information with th e installation of latest version
Which function will use in sql server instead of to_char ( there is no in-built function in sql server)
Check year() once
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 think an easier solution is to aggregate the sum amount divided by the number of years and use a where clause to limit the billing year.
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 ;
Did you try running the query? That doesn't meet the answer/criteria. You need to account for years customers were not billed, which changes the averages. Yes we are trying to find the average billing amount, but you need to include 0 for the years the customer wasn't billed which is broken down in this video.
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 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
Hi, do snowflake videos also bro
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
I like SQL a lot.
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;
More videos please🙂
noted, will try
Hello sir plz help me to choose career. I hv done BE n mtech in CS. I have teaching experience around 7+ year. I am on break from last 4 yr for my kids. Now I want to restart my career as a devloper . I have strong knowledge of core Java n SQL queries. Which type of job profile best suited me?
Thanks
Your welcome 🙏🏼
I couldn't download the dataset just the SQL script available for download.
Why we weren't change null to 1?,it can be show 1 instead of null value?
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
Imagine a customer wasn't actually a customer during the defined billing period. A 0 wouldn't indicate no billing for that year because no billing data would be possible. A customer that had a large outlier billing amount for only one out of the 4 billing periods would yield a misleading result. For business analysis, I'd question the purpose of the results of the query.
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?
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
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.
we can solve it without case statement too. there is always multiple ways to solve a sql problem. just need to spend some time to think alternate ways
@@techTFQ That's True. Thank you for all your efforts in uploading these videos.
We can use CTE to create a temp table carrying distinct years from the input data and then join it back with the data again to compute the sum n count values without running case for each year !!
@@naash9137 With cross join? That worked for me. But if there are millions of customers not sure if cross join is efficient.
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
It looks like the example accidentally added one to the count for every account, even if they didn’t have a $0 billing year ($600/4 = $200)