Ultimate SQL Challenge: 50 Advanced Interview Questions Mastered! - Leetcode 1398 | Data Science

Welcome to the first episode of our groundbreaking series designed to take your SQL skills to the next level! In this series, we're tackling 50 of the most challenging and insightful interview questions on #advanced SQL, aimed at transforming you from an intermediate user to a bona fide #sql expert. Whether you're preparing for a job #interview , seeking to enhance your data manipulation capabilities, or simply passionate about mastering the complexities of SQL, this series is your ultimate roadmap.

SQL Schema:
Create table If Not Exists Customers (customer_id int, customer_name varchar(30))
Create table If Not Exists Orders (order_id int, customer_id int, product_name varchar(30))
Truncate table Customers
insert into Customers (customer_id, customer_name) values ('1', 'Daniel')
insert into Customers (customer_id, customer_name) values ('2', 'Diana')
insert into Customers (customer_id, customer_name) values ('3', 'Elizabeth')
insert into Customers (customer_id, customer_name) values ('4', 'Jhon')
Truncate table Orders
insert into Orders (order_id, customer_id, product_name) values ('10', '1', 'A')
insert into Orders (order_id, customer_id, product_name) values ('20', '1', 'B')
insert into Orders (order_id, customer_id, product_name) values ('30', '1', 'D')
insert into Orders (order_id, customer_id, product_name) values ('40', '1', 'C')
insert into Orders (order_id, customer_id, product_name) values ('50', '2', 'A')
insert into Orders (order_id, customer_id, product_name) values ('60', '3', 'A')
insert into Orders (order_id, customer_id, product_name) values ('70', '3', 'B')
insert into Orders (order_id, customer_id, product_name) values ('80', '3', 'D')
insert into Orders (order_id, customer_id, product_name) values ('90', '4', 'C')

Pandas Schema:
data = [[1, 'Daniel'], [2, 'Diana'], [3, 'Elizabeth'], [4, 'Jhon']]
customers = pd.DataFrame(data, columns=['customer_id', 'customer_name']).astype({'customer_id':'Int64', 'customer_name':'object'})
data = [[10, 1, 'A'], [20, 1, 'B'], [30, 1, 'D'], [40, 1, 'C'], [50, 2, 'A'], [60, 3, 'A'], [70, 3, 'B'], [80, 3, 'D'], [90, 4, 'C']]
orders = pd.DataFrame(data, columns=['order_id', 'customer_id', 'product_name']).astype({'order_id':'Int64', 'customer_id':'Int64', 'product_name':'object'})

In this first episode, we kick things off with a deep dive into a question that stumps many aspiring SQL professionals. We'll explore the intricacies of advanced query writing, focusing on techniques that go beyond the basics of SELECT statements and WHERE clauses. You'll learn about advanced joins, window functions, recursive queries, and much more.

What to Expect:

Detailed Explanations: We don't just solve the question; we dissect it. You'll understand not only the 'how' but also the 'why' behind each solution, ensuring you can apply these principles to a variety of SQL challenges.
Step-by-Step Approach: Our tutorials are designed to be easy to follow. We break down complex concepts into manageable parts, making advanced SQL more accessible than ever.
Real-World Applications: These aren't just theoretical exercises. Each question is selected for its relevance to real-world SQL problems, ensuring you gain practical skills that you can apply in a professional setting.
Interactive Learning: Engage with the content through quizzes and challenges presented throughout the series. Test your knowledge, practice new skills, and solidify your understanding of advanced SQL techniques.
Community Support: Join a growing community of SQL enthusiasts and professionals. Share insights, ask questions, and receive feedback through the comments section below each video.

Why This Series?

SQL remains one of the most in-demand skills in the tech industry, and for a good reason. It's the backbone of data analysis, database management, and many forms of programming. As data continues to drive decision-making in businesses worldwide, the ability to manipulate and retrieve data efficiently becomes increasingly valuable. This series is designed to equip you with these skills, ensuring you're not just ready for your next job interview but also prepared to tackle real-world data challenges.

Who Should Watch?

Intermediate SQL users looking to advance their skills.
Job seekers preparing for technical interviews.
Data professionals seeking to deepen their understanding of SQL.
Anyone with a passion for data and an eagerness to learn.
How to Get the Most Out of This Series:

Practice Along: Have your SQL environment ready to try out each question as we go through them.
Engage: Leave comments with your own solutions, questions, or insights.
Stay Consistent: Follow the series for a comprehensive understanding of advanced SQL.

Subscribe and Join Us on This Journey

[devrajpatidar]

hey sir nice video
here is my approach :-

with cte as (
select customer_id,
sum(case when product_name in ('A', 'B') then 1 else 0 end) as flag,
sum(case when product_name = 'C' then 1 else 0 end) as flag1
from Ords
group by customer_id
)
select cte.customer_id, cust.customer_name from cte
inner join cust
on cust.customer_id = cte.customer_id
where flag = 2 and flag1 = 0;

[Mehtre]

Hello sir, can we put all examples in one doc. N share. So it will great help. Its request.

[architectaq]

-- My solution
with cte as
(select c.customer_id, c.customer_name, o.product_name
from customers c
join orders o on c.customer_id = o.customer_id
where o.product_name in ('A', 'B', 'C'))

select customer_id, customer_name
from cte
where customer_id not in (select distinct customer_id from cte where product_name = 'C')
group by 1, 2
having count(distinct product_name) = 2;

[IMAJAY]

ORACLE SQL CODE
SELECT o.CUSTOMER_ID,
LISTAGG(o.PRODUCT_NAME, ', ') WITHIN GROUP (ORDER BY o.PRODUCT_NAME) AS products_bought
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
GROUP BY o.customer_id
HAVING products_bought LIKE '%A, B%'
and products_bought not like '%C%'
;