SQL Interview Questions & Answers | Day 4: Berlin Web Series Scenario

"Berlin is back, but before the Royal Mint, his crew was hitting the streets of Europe! 🕶️

In Day 4 of our SQL Series, we’re diving into Berlin’s Secret Diary. Keila needs to extract critical intelligence on the crew's heist history. We will solve a real-world business logic problem using SQL, focusing on aggregations, distinct counts, and ranking.

🔍 What You’ll Learn Today:
How to calculate total visits per member using COUNT.

How to find the 'Top Hunting Ground' (Most visited area) using CTEs and Window Functions.

How to generate a clean list of 'Unique Loot' using STRING_AGG or DISTINCT.

💻 The Problem Scenario:
Berlin has arranged a lunch for the team and handed his diary to Keila. We need to find:

Total number of theft visits per member.

The area each member visited most frequently.

A distinct list of resources stolen by each member (Cars, Artifacts, Jewelry).

Scripts:
--select * from Heist;
with total_thefts as(
Select name,count(1) as Total_thefts from Heist
group by name
),
area_frequency as (
Select name, area_code,count(area_code) as frequncy
,rank() over(partition by name order by count(area_code) desc) as rn
from Heist
group by name, area_code)
, distinct_resources as (
Select distinct name,resources from Heist),
agg_resources as(
Select name,group_concat(resources,',') as resources_used from distinct_resources group by name)

Select fv.name,fv.area_code,fv.frequncy,a.resources_used, t.Total_thefts
from area_frequency fv
inner join total_thefts t
on t.name=fv.name
inner join agg_resources a
on a.name=fv.name
where fv.rn=1;

create table Heist (
name varchar(20),
city varchar(30),
area_code int,
resources varchar(20));

insert into Heist values
('Cameron','Berlin',10115,'Gold'),
('Cameron','Berlin',10115,'Cash Bag'),
('Cameron','Berlin',10179,'Gold'),
('Fred','Berlin',10179,'Car'),
('Fred','Berlin',10179,'Weapons'),
('Fred','Berlin',10115,'Gold'),
('Fred','Berlin',10115,'Car');

--select * from Heist;
#SQL #SQLInterview #BerlinNetflix #DataAnalytics #CodingChallenge #LearnSQL"

Видео SQL Interview Questions & Answers | Day 4: Berlin Web Series Scenario канала Data-2-Dollars