Very Interesting SQL Interview Question Asked by Udaan | Power of Self Join | Data Analytics

In this video we will discuss a very interesting SQL interview question asked by Udaan. We will use self join to solve this question. Please comment your solution if you have a better one.

Zero to hero(Advance) SQL Aggregation:

Most Asked Join Based Interview Question:

Solving 4 Trick SQL problems:

Data Analyst Spotify Case Study:

Top 10 SQL interview Questions:

Interview Question based on FULL OUTER JOIN:

Playlist to master SQL :

Rank, Dense_Rank and Row_Number:

script:
create table business_city (
business_date date,
city_id int
);
delete from business_city;
insert into business_city
values(cast('2020-01-02' as date),3),(cast('2020-07-01' as date),7),(cast('2021-01-01' as date),3),(cast('2021-02-03' as date),19)
,(cast('2022-12-01' as date),3),(cast('2022-12-15' as date),3),(cast('2022-02-28' as date),12);

#sql #interview #selfjoin

[damoncaffrey]

Hi Ankit, first of all thank you so much for posting such an amazing content, got to learn a lot from your channel. Here's my solution :-

WITH cte1 AS(
SELECT MIN(YEAR(business_date)) as "year", city_id FROM business_city
GROUP BY city_id)

SELECT year, COUNT(city_id) AS no_of_city FROM cte1
GROUP BY year;

[varunas]

Good one as always..

here's my approach on SQL server:


with city_count as (select *,
over(partition by city_id order by year(business_date)) [new city count per year]
business_city)

select YEAR(business_date), SUM([new city count per year])
from city_count
where [new city count per year] = 1
group by YEAR(business_date)

[SonuSagar-gnrf]

Hi Ankit, thank you for posting such questions. Appreciate your efforts.
With CTE AS (
Select *,
Year(business_date) AS Year_business,
row_number() over (partition by city_id order by business_date) AS rn
from business_city
)
Select Year_business, Count(1) as new_cities
from CTE
where rn=1
group by Year_business

[sahilummat]

Hello Ankit Sir
Love your videos

Another solution from my end

;with cte as (
select date_trunc('Year', business_date), year(business_date) as yr,
city_id,
by city_id) as first_order_year
from business_city
order by business_date
)
select yr,
sum(case when yr =first_order_year then 1 else 0 end ) as new_cities_served
from cte
group by yr

[Useracwqbrazy]

....below is my solution...

with cte as(select year(business_date) as year, city_id from business_city),
cte2 as(
select *,
row_number() over(partition by city_id order by year) as cnt from cte
),
cte3 as(select year, city_id from cte2 group by y, city_id having max(cnt)=1)

select year, count(city_id) from cte3 group by year

am really glad ANKIT SIR i started watching ur videos...this question i solved in first attempt....all thanks to you....i learned the multiple cte usage in this

[MixedUploader]

Ankit thanks for the solution with self join. Here I tried with lag below:
with cte as(select *,
lag(business_date, 1) over(partition by city_id order by business_date) as prev_city_date
from business_city)
select datepart(year, business_date) as year, count(city_id) as new_cities
from cte
where prev_city_date is null
group by datepart(year, business_date)
order by datepart(year, business_date)

[cistales]

select YEAR(business_date), count(city_id)
from(select *, DENSE_RANK()over(partition by city_id order by business_date) rn from business_city) x
where x.rn=1
group by year(business_date)

[channeladventure]

Hi Ankit, very smart solution!

here is another small solution using ranking of city_id on business year and couting those cities only with rank 1:

with cte as (select cast(business_date as year) as year, rank() over(partition by city_id order by business_date) as city_rank from business_city)
select year, count(case when city_rank=1 then 1 else null end) as new_cities from cte group by year

[mohdtoufique]

Hi Ankit...Thanks for the content...My Approach
WITH CTE AS(
SELECT *, DENSE_RANK()OVER(PARTITION BY city_id ORDER BY business_date)AS rn, year(business_date) AS year_date
FROM business_city)
SELECT year_date, COUNT(city_id) AS new_cities
FROM CTE
WHERE rn=1
GROUP BY year_date

[harishkanta]

Ankit bhai this worked for me

with cte as (select city_id, year(business_date) as bus_yea, rank() over(partition by city_id order by business_date) as rnk from bus)
, cte2 as (select city_id, min(bus_yea) as t from cte group by city_id)
select count(1), t from cte2 group by t;

[mantisbrains]

select year, sum(rn) as new_cities from
(select extract as year, rn from
(select extract(year from business_date), city_id, row_number() over (partition by city_id order by extract(year from business_date))rn from
business_city)a
where rn = 1)j
group by year;

Thank you, Ankit !

[kumarsharan]

Hi Ankit,
I sticked to the basic functions. Could you please tell if it is correct or not?

select year(min_date), count(*) from
(select min(business_date) min_date, city_id from business_city
group by city_id) s
group by year(min_date);

[DRahulRajuYadav]

i did it by this approach

with cte as (select distinct city_id, min(business_date) as first_date from business_city
group by city_id)
select year(first_date) as year, count(city_id) as cnt from cte
group by year

[maruthikrishnappa]

Hi Ankit, Thanks a lot for your videos. It helped me a lot. Here is my query written in MySql

with cte as
(select year(business_date) as yr, city_id from business_city group by city_id)
select yr, count(city_id) as city_count from cte group by yr;

[vikaskumar-qrtj]

My approach using lag function Ankit :-
With cte as
(
Select datepart(year, business_date) as yr, city_id
from business_city
)
Select yr, count(case when prev_yr=0 then city_id end) as cnt from (
Select city_id, yr, lag(yr, 1, 0) over(partition by city_id order by yr asc) as prev_yr from cte
) xx group by yr;
Thanks for posting amazing content.

[manojsrikanth]

Hi, Ankit, Thank you..
select a.year,
sum(case when a.rn = 1 then 1 else 0 end) new_cities from
(select year(business_date) year, city_id, row_number() over (partition by city_id) rn
from business_city) a
group by 1

[anilag]

i did this way:
with rn as
(
select * from (
select year(business_date) as year, city_id, row_number() over(partition by city_id order by business_date) as rn1 from business_city) a
where a.rn1=1
)
select year, count(city_id) from rn group by year

[ankush_in_sync]

Hi Ankit, here can we use the approach of one of the problem where we had found out the first_visit_flag and repeat_visit_flag from orders table. There we had used the logic sum(case when order_date=first_visit_date then 1 else 0 end

[rahulkushwaha]

As always : First of all thank you so much bhaiya,
This is using row_number(), but your solution is awesome and epic use of self join.. .

with cte as
(
select business_date, city_id,
row_number() over(partition by city_id order by left(business_date, 4)) as rnum
from business_city )

select left(business_date, 4) as Year, count(rnum) as no_of_new_city from cte
where rnum=1
group by 1
order by 1;

[ruhansiddiqui]

Hi Ankit thanks for this question
with cte as(
select year (business_date), dense_rank() over(partition by city_id order by business_date) as dr )
select year (business_date), count(case when dr=1 then 1 else 0 end) as cities from cte group by city_id order by year(business_date)