Data Analyst Interview Problem - SQL Interview Query 25 | SQL Problem Level "EASY"

30DaySQLQueryChallenge is a series of 30 videos covering 30 SQL Interview Queries. This is the 25th video in this series. This video series aims to provide 30 SQL Queries that can be asked during SQL Interviews. I will explain how to solve and address such SQL queries during interviews in these videos.
Learn and Practice SQL on LearnSQL platform below(Checkout the free postgresql-window-functions course only during March 2024):
learnsql.com/?ref=thoufiqmoha...
Let's follow the below routine to make the best use of it:
1. Watch the CZcams video (first half) to understand the problem statement.
2. Go to my discord server (link below), download the dataset for each problem, and try solving it yourself.
3. Share your solution on Discord and discuss different solutions and issues on my Discord server.
4. Watch the second half of my CZcams video to find my solution to the problem.
5. Share it with your contacts and spread the knowledge.
DOWNLOAD the Dataset from below:
Discord server: / discord
Blog website: techtfq.com/blog/30daysqlquer...
Timeline:
00:00 Intro
00:10 Understanding Problem Statement
00:54 Solution to the SQL Problem
Thanks for participating in this challenge!
Good luck and Happy Learning!

[@rajkumarrajan8059]

Simple and elegant

[@aahsankhan4502]

Sir, what an Explanation ❤
Your way of teaching is amazing.

[@anilkapkoti296]

A good 1

[@malcorub]

Since techTFQ went easy on us today and it's a holiday for many of you, here is a poem regarding the point of the video by Chat GPT.
"In the realm of SQL, where logic prevails,
The Case Statement's prowess seldom fails.
For aggregation it's used, a masterful feat,
Summing up values, making data complete.
Yet within its folds, a secret it hides,
String functions reside, where data abides.
Checking for spaces, and text case in tow,
The Case Statement thrives, letting insights flow." 🙂

[@kyleebrahim8061]

Although simple this challenge requires initial basic analytical thinking before querying, good one.

[@KAshIf0o7]

We can also use left function here too

[@praveensahu7625]

Please also share the dataset resources so we can practice on that.

[@govindjha4457]

Happy Holi to all

[@blse2000]

SQL SERVER SOLUTION:
SELECT store_id,
SUM(CASE WHEN product_1 LIKE '%Apple%' THEN 1 ELSE 0 END) AS product_1,
SUM(CASE WHEN product_2 LIKE '%[ ]Apple%' OR product_2 LIKE 'Apple%' THEN 1 ELSE 0 END) AS product_2
FROM product_demo
GROUP BY store_id

[@niharikamedam]

with cte as (
select Store_id,substring(product_1,1,5) as p1
,substring(ltrim(product_2),1,5) as p2
from product_demo),
cte2 as (
select *,case when p1 like 'Apple' then 1 else 0 end as p1flag
,case when p2 like 'Apple' then 1 else 0 end as p2flag
from cte)
select store_id,sum(p1flag) as prod1,sum(p2flag) as prod2 from cte2
group by store_id

[@yashwanthgaming137]

Que: In a month vendor is null then replace with previous month vendors.
Input:
Year | Vendor | Amt
1-2021 | A | 100
1-2021 | B | 200
1-2021 | C | 300
2-2021 | Null | Null
3-2021 | A1 | 210
3-2021 | B1 | 230
4-2021 | Null | Null
output:
Year | Vendor | Amt
1-2021 | A | 100
1-2021 | B | 200
1-2021 | C | 300
2-2021 | A | 100
2-2021 | B | 200
2-2021 | C | 300
3-2021 | A1 | 210
3-2021 | B1 | 230
4-2021 | A1 | 210
4-2021 | B1 | 230
Need to solve this question?

[@anchal7876]

select store_id,
count(case when REPLACE(REPLACE(REPLACE(LOWER(product_1), '-', ''), '_', ''), ' ', '') like 'apple%' then 1 end) as product_11,
count(case when REPLACE(REPLACE(REPLACE(LOWER(product_2), '-', ''), '_', ''), ' ', '') like 'apple%' then 1 end) as product_12
from product_demo
group by store_id

[@DEwithDhairy]

PySpark 30 Days Challenge of these problems :
czcams.com/play/PLqGLh1jt697xzk9LCLL_wFPDZi_xa0xR0.html

[@Mayank-jw9yy]

SELECT store_id,
SUM(CASE WHEN UPPER(product_1) LIKE 'APPLE%' THEN 1 ELSE 0 END) as product_1,
SUM(CASE WHEN LTRIM(UPPER(product_2)) LIKE 'APPLE%' THEN 1 ELSE 0 END) as product_2
FROM
product_demo
GROUP BY store_id;

[@shivamgoyal2213]

select store_id,
sum(case when UPPER(product_1) LIKE 'APPLE%' then 1 else 0 end) as product1,
sum(case when UPPER(Product_2) LIKE 'APPLE%' then 1 else 0 end) as product2
from product_demo
group by store_id;

[@Mayank-jw9yy]

this soltion eats 1 apple in both store_id 1 & 2......does not match with exact soltion.

[@Rameshkumar-dk8me]

Happy Holi @Thoufig

[@techTFQ]

Thanks buddy, wish you the same :)