Solving SQL Interview Query for Data Analyst asked by a Product based company 

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;

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

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

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

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!

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

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!

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

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!

Great explanation as usual, thanks for great walkthrough 💯

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 Vid !!!! I really enjoy your way of explaining complex things so easily !!! Keep going !!

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

such a wonderful video.. thanks for sharing.

Wonderful explanation Toufiq 👌👌

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

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

Wonderful thoufique . It was really helpful

Great work. Lots of fun. 👍🏽

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;

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

Superb explanation 👌 👏 👍

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

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

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

Nice video. Very clear explanation. Keep it up.

Excellent explanation bro...

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

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

Great practice

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.

You nailed it.

Good 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 😇

Big thanks bhai, you are doing amazing job

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

very interesting

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

amazing.

Thank you thoufiq for the question.

very nice

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

Please solve more interview questions like this

interesting, i still have a lot to learn about SQL

🙂Very Nice Explanation.

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

Thanks for explanation ☺

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

wow an easy question

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)

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.

Thanks

I like to watch this

Which one u r using for execution

More videos please🙂

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

I like SQL a lot.

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.

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.

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;

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

Hi, do snowflake videos also bro

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.

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! :))

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

Great explanation sir....👍 I have doubt Can't we use aggregate function to sum_2019,20,21 to print average of them

Sir, kindly upload Date and Time functions in SQL which are using organization

Hi, I an interview I got a question, alternative commands for full outer join

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

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

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.

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

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

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

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

Hi bro I 2ant to learn Oracle SQL ...can i get full information with th e installation of latest version

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?

🔥

Hey i completed 4 hour SQL course from freecodecamp ,where should i practice? Any project for data analytics job ? What's next

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

Do we have playlist for this

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.

Thank you for a video! I was interested how to write a query for a common case. I used years iterator with generate_series function. ;with cte as ( select * from generate_series(2019, 2021) year, (select distinct customer_id, customer_name from billings) as x ) select cte.customer_id, cte.customer_name, round(avg(case when b.billing_amount is null then 0 else b.billing_amount end), 2) as billing_amount_in_year from cte left join billings b on date_part('year', b.billing_cretation_date)::int = cte.year and b.customer_id = cte.customer_id group by cte.customer_id,cte.customer_name order by cte.customer_id

Which function will use in sql server instead of to_char ( there is no in-built function in sql server)

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

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;

How are we getting count as 3 for all the cells

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 ;

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

too ez question

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?

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 ?

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?

hi I need one more example of SQL YOY contribution% and rolling average if you have year from 2017-2022...and random sales numbers... Pls can u make one more video as your explanation is quite simple to understand This was a interview question

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

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