SQL Challenge - Data Analyst Interviews!: Solve SQL Question LIKE a PRO! EP SQL - 04/50

Stay connected for the latest updates, job opportunities, and data analysis tips!

JOIN the SQL 3 Days 12 hour live workshop Batch-02

Let's grow together!

My Github to practice these questions

Ready to CRUSH your Data Analyst or Business Analyst interview? Join me on this 50-DAY SQL INTERVIEW CHALLENGE!

In this video, we'll dive DEEP into an ACTUAL Interview Question for Business Analyst and Data Analyst interview question. I'll walk you through:

Understanding the problem statement: We'll break down the question clearly and identify key requirements.
Building the optimal SQL query: Step-by-step, we'll craft an efficient and accurate solution using JOINs, aggregations, and filters.
Explaining your thought process: Learn how to articulate your problem-solving approach and showcase your SQL expertise.
BONUS TIPS & TRICKS: Gain valuable insights for acing your SQL interview with confidence.
Don't forget to:

Like this video and subscribe for more daily challenge videos!
Leave a comment with your own approach to the question.
Share this video with your fellow job seekers!
This is just the beginning! Subscribe now and stay tuned for Day 2's challenge!

My Github

Music cc: YouTube-Channel Liborio Conti

[yogeshchouhan]

query without using cte and join : ->
select * from (
select category, product, sum(spend),
dense_rank() over(partition by category order by sum(spend) desc) as 'dnr1'
from orders1
where category in (
select category from
(
select category, sum(spend),
dense_rank() over(order by sum(spend) desc) as 'dnr' from orders1
group by category
) as t1
where dnr <= 2
)
group by category, product
) as t2
where dnr1<=2

[VenkateshMarupaka-gnrp]

My solution:

WITH CTE AS (SELECT category, product, SUM(spend) OVER(PARTITION BY category ORDER BY category) AS cate_sum,
SUM(spend) OVER(PARTITION BY category, product ORDER BY category) AS prod_sum
FROM orders),

cte1 AS(select *, DENSE_RANK() OVER(ORDER BY cate_sum DESC) AS category_rk,
DENSE_RANK() OVER(PARTITION BY category ORDER BY prod_sum DESC) AS prod_rk
FROM CTE)

SELECT DISTINCT category, product, prod_sum
FROM CTE1
WHERE category_rk<=2 AND prod_rk <=2

[sindhushankar]

for the task we have to change drn = 1, pdrn =1 and in the dense rank it should be changed to asc we will get answer

[vinothkumars]

Very interstring one for Dense rank window function

[iAmLeeFr]

with cte as (
select category, total_spends_category from
(
select
category, sum(spend) as total_spends_category,
dense_rank() over (order by sum(spend) desc) as rnk
from orders
group by category
) as subquery
where rnk<=2
)

select category, product from
(select category, product, sum(spend) as total_spend_by_product,
dense_rank() over (partition by category order by sum(spend) desc) as brnk
from orders group by category, product) as subquery2
where brnk<=2
and category in (select category from cte)



Instead of using Join, we can use inner query like this right ?