Tiger Analytics Set of 2 SQL Interview Problems | SQL For Beginners

In this video we will discuss 2 SQL interview problems asked in Tiger Analytics data analyst Interview. Here is the script to try yourself

00:00 Problem 1
04:48 Problem 2

problem 1:

CREATE TABLE flights
(
cid VARCHAR(512),
fid VARCHAR(512),
origin VARCHAR(512),
Destination VARCHAR(512)
);

INSERT INTO flights (cid, fid, origin, Destination) VALUES ('1', 'f1', 'Del', 'Hyd');
INSERT INTO flights (cid, fid, origin, Destination) VALUES ('1', 'f2', 'Hyd', 'Blr');
INSERT INTO flights (cid, fid, origin, Destination) VALUES ('2', 'f3', 'Mum', 'Agra');
INSERT INTO flights (cid, fid, origin, Destination) VALUES ('2', 'f4', 'Agra', 'Kol');

Problem 2:

CREATE TABLE sales
(
order_date date,
customer VARCHAR(512),
qty INT
);

INSERT INTO sales (order_date, customer, qty) VALUES ('2021-01-01', 'C1', '20');
INSERT INTO sales (order_date, customer, qty) VALUES ('2021-01-01', 'C2', '30');
INSERT INTO sales (order_date, customer, qty) VALUES ('2021-02-01', 'C1', '10');
INSERT INTO sales (order_date, customer, qty) VALUES ('2021-02-01', 'C3', '15');
INSERT INTO sales (order_date, customer, qty) VALUES ('2021-03-01', 'C5', '19');
INSERT INTO sales (order_date, customer, qty) VALUES ('2021-03-01', 'C4', '10');
INSERT INTO sales (order_date, customer, qty) VALUES ('2021-04-01', 'C3', '13');
INSERT INTO sales (order_date, customer, qty) VALUES ('2021-04-01', 'C5', '15');
INSERT INTO sales (order_date, customer, qty) VALUES ('2021-04-01', 'C6', '10');

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:

#sql #tigeranalytics #datanalytics

[pratibhasaha]

My solution to the first one :

select distinct cid, first_value(origin) over(partition by cid order by fid ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as actual_origin,
last_value(Destination) over(partition by cid order by fid ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as final_destination
from flights

[ankitbansal]

Please do like the video for more interview questions.

[monikadialani]

Hey Ankit!
Forever grateful to you for the ultimate content and for making SQL easier for us.
Here is my solution to the 1st question.

with cte as (
select *,
rank() over (partition by cid order by fid) as rnk
from flights
)
select cid,
max(case when rnk=1 then origin end) as origin,
max(case when rnk=2 then Destination end) as destination
from cte
group by cid

[AmitDevSocial]

Hi Ankit,
You're truly a gem for people like me. god bless you. I tried 2nd question using this approach. please check and share your feedback

with cte as(
select min(order_date) as date, customer from sales group by customer
)
select date, count(*) as no_of_new_cus from cte group by date

[ronnykingpin]

Great explanation. one more command to add in the first question in join condition-
o.cid = d.cid you missed. so for any new records having similar origin/destination records will be duplicated.

[muditmishra]

Hi Ankit, my solution for 1 question is :

WITH cte1 AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY cid ORDER BY fid) as rk_origin,
ROW_NUMBER() OVER (PARTITION BY cid ORDER BY fid DESC) as rk_dest
FROM flights
)
SELECT
c1.cid,
c1.origin AS first_origin,
c2.destination AS last_destination
FROM cte1 c1
JOIN cte1 c2 ON c1.cid = c2.cid
WHERE c1.rk_origin = 1 AND c2.rk_dest = 1;


This approach ensures that regardless of how many entries each cid has, you always get the starting point and the final destination in the sequence based on fid ordering.

[Shoaibsaifi]

Hi Ankit Sir,
Here is an additional alternative:

SELECT A.First_visit_date,
Count(DISTINCT customer) AS New_customer
FROM (SELECT customer,
Min(order_date) AS First_visit_date
FROM sales
GROUP BY customer) A
GROUP BY A.First_visit_date

[Reacher]

Thanks Ankit for the problem . Here's my take:

Problem 1 Sol:
select cid, max(or_rnk) as source, max(det_rnk) as destination from
(select *, first_value(origin) over (partition by cid order by fid) as or_rnk,
first_value(destination) over (partition by cid order by fid desc) as det_rnk
from flights) a
group by 1
order by 1;

Problem 2 Sol:
WITH CTE AS
(select *, DENSE_RANK() OVER (ORDER BY ORDER_dATE) AS DATE_RANK
from sales)
SELECT A.ORDER_DATE, COUNT(*) AS NEW_CUSTOMER_COUNT
FROM CTE A LEFT JOIN CTE B
ON A.DATE_RANK > B.DATE_RANK AND A.CUSTOMER = B.CUSTOMER
WHERE B.ORDER_DATE IS NULL
GROUP BY 1;

[Hope-xbjv]

1.Logic fail when flight stop more than one stop like del, hyd, kol, blr (two stop hyd, kol before blr) thats why i write this way

;with cte1 as
(
select cid, origin from flights
except
select cid, destination as origin from flights
), cte2 as
(select cid, destination from flights
except
select cid, origin as destination from flights
) select c1.cid, c1.origin, c2.destination from cte1 c1
inner join cte2 c2
on c1.cid = c2.cid

[preetkothari]

Great explanation. But I think we can have an easier solution as well. I think in this solution of mine I am utilizing a loophole of the group by clause.
WITH cte AS (SELECT order_date, customer FROM sold
GROUP BY customer)
SELECT order_date, COUNT(customer) AS new_customer FROM cte
GROUP BY MONTH(order_date);
basically group by will automatically deliver us first row if no aggregate function is used in the CTE. Thus, we automatically get the first visit of the customer without using the row_number() window function.

[ArpitaGoswami-mtnw]

2nd solution alternative approach

with cte as (
select
order_date, customer,
row_number() over (partition by customer order by order_date) as rn
from Sales)
select order_date, count(rn) as new_customer
from cte
where rn =1
group by order_date

[cracko]

Hi @Ankit
Your content has helped me a lot in Job switch
Please keep uploading videos, the way you explains makes a difficult question look like a easy peasy.
You have helped me in building confidence in MySql.

Thanks a lot Sir.

[invincible]

Hi Ankit Sir, I have tried a solution like this :
Problem 1 :
with cte as (select *, row_number() over (partition by cid) as rn from flights )
select cid, destination as final_destination from cte where rn in (select max(rn) from cte group by cid) ;

Problem 2:
with cte as (
select customer, order_date,
dense_rank() over (partition by customer order by customer, order_date asc) as drnk
from sales
)
select month(order_date), count(customer) as new_customer_count from cte where drnk = 1
group by month(order_date);

[Dhruvin__]

Hi Ankit,
Here is my solution for Prblm 1. I felt in the solution you gave, we missed a case where a new flight with new cid might originate from HYD. That case, there will be a unnecessary join as well. Let me know if I'm missing something in the logic.


select f1.cid, f1.origin, f2.Destination from
Practice.dbo.flights f1
left join Practice.dbo.flights f2 on f1.cid=f2.cid and f1.fid<f2.fid
where f2.cid is not null

[vandanaK-mhzo]

Ques1: select o.cid, o.origin, d.destination
from flights o
inner join flights d
on o.destination = d.origin
and o.cid = d.cid;

Ques2: with cte as (
select customer, min(order_date) as order_date
from sales group by customer)
select order_date, count(distinct(customer)) from cte group by order_date ;

[SanjeevKumar-pn]

For 1st question, What in case if there are 3 or more records for some id's. Let's say for id = 1, we have BNG -> HYD, then HYD -> DEL, then DEL -> PTN. How to solve this scenario.
Thank you for your all the work and community help you are doing in world of SQL.

[AbhishekSharma-ujxi]

Hi @Ankit,
My approach for second solution would be like below. We can extract date also from order_date rather than using substr too.

select substr(a.order_date, 6, 2), count(distinct customer)
from customers a
where customer not in (select customer from customers where substr(a.order_date, 6, 2) > substr(order_date, 6, 2))
group by 1
order by 1

[lnwnlnx]

Hello sir
my code for 1st query -
with cte1 as (
select cid, case when fid=min(fid) over(partition by cid) then origin end as origin
from
flights
group by cid, fid, origin, Destination)
, cte2 as (select cid,
case when fid=max(fid) over(partition by cid) then Destination end as destination
from
flights
group by cid, fid, origin, Destination)
select cte1.cid, cte1.origin, cte2.destination from
cte1 join cte2 on cte1.cid = cte2.cid and origin is not null and destination is not null

[lnwnlnx]

Hello sir
for 2nd ques -
select order_date, count(customer)
from
(
select min(order_date) as order_date, Customer
from sales
group by customer
) a
group by (order_date)

[mohammeddilshad]

first solution another approach especially if there are multiple flights by 1 cust
with cte as (
select *, row_number() over(partition by cust_id order by flight_id) as rn
from flight order by flight_id asc
)
select cust_id,
first(origin) as start,
last(destination) as end
from cte
group by cust_id