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:
• All About SQL Aggregat...
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Trick SQL problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
#sql #tigeranalytics #datanalytics

[@monikadialani4291]

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

[@ankitbansal6]

Please do like the video for more interview questions.

[@grzegorzko55]

The best

[@nicky_rads]

Awesome video, great explanation for each query and associated logic !

[@avi8016]

Great video sir💯 Thankyou for bringing such wonderful interview questions 🙏

[@pratibhasaha4380]

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

[@rahulmehla2014]

select distinct cid, first_value(origin) over(partition by cid order by fid ) as actual_origin, first_value(Destination) over(partition by cid order by fid desc) as final_destination from flights this only will get u the same result

[@balakumarankannan3813]

@@rahulmehla2014 nice, this is more scalable

[@cracko3483]

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.

[@ankitbansal6]

Glad to know 😊

[@puroshotamsingh5713]

if we have more than two flights of each customers then below will be more dynamic solution: with cte as ( select cid, origin, Destination, Rank() over (partition by cid order by fid) as rnk from flights), cte1 as ( select cid, origin, Destination, Rank() over (partition by cid order by fid Desc) as rnk from flights), cte2 as ( select c1.cid, c1.origin, c2.Destination from cte c1 inner join cte1 c2 on c1.cid=c2.cid where c1.rnk=1 and c2.rnk=1) select * from cte2;

[@AbhishekSharma-uj7xi]

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

[@ankitbansal6]

It's better to use date functions on date columns . Substring is best suited for string data types

[@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.

[@vandanaK-mh9zo]

thanks for sharing

[@preetkothari5592]

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.

[@Tech_world-bq3mw]

--my solution(its dynamic) with flight as( select *,count(*) over(partition by cid) totalflights, row_number() over(partition by cid)as flightNumber from flights ) select cid,MAX(origin) as origin,max(destination) as destination from( select cid, case when flightNumber=1 then origin end as origin, case when flightNumber=totalflights then destination end as destination from flight) group by cid

[@Shoaibsaifi236]

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

[@AbhishekKumar-fn7sq]

with cte as (select cid,origin,destination,lead(origin,1) over(order by cid) as origin_1, lag(origin,1) over(order by cid) as origin_2 from flights) select cid,max(case when destination=origin_1 then origin end) as origin, max(case when destination!=origin_2 then destination end) as destination from cte group by cid

[@biswanathprasad1332]

more generalised sol for 1st problem: (to handle more than 2 rows for a flight) with tab as (select *,lag(origin) over (partition by cid order by (select null)) prev_origin, lead(Destination) over (partition by cid order by (select null)) nxt_des from flights) select cid,max(case when prev_origin is null then origin end) as ori,max(case when nxt_des is null then Destination end) as Dest from tab group by cid 2nd solu: with first_date as (select customer,min(order_date) as first_date from sales group by customer) select s.order_date,sum(case when order_date=first_date then 1 end) as new_cust from sales s inner join first_date fd on s.customer=fd.customer group by s.order_date

[@user-dw4zx2rn9v]

Nice question

[@sobhiksaha7140]

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;

[@seemapinto2001]

sol 1 helps when there are multiple layovers

[@sahilummat8555]

Hey Ankit My Approach towards the problem with cte as ( select * ,ROW_NUMBER()over(partition by cid order by fid) as rn from flights ) select cid,max(case when rn=originf then origin else null end )as Origination ,max(case when rn=destinationf then Destination else null end )as Desination from ( select *,min(rn)over() as originf, max(rn)over() as destinationf from cte)a group by cid with cte as ( select * , min(order_date)over(partition by customer) as first_purchase from sales) select order_date,SUM(flag) as new_customers from ( select *, case when order_date=first_purchase then 1 else 0 end as flag from cte )a group by order_date

[@ArpitaGoswami-mt7nw]

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

[@muditmishra9908]

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.

[@vinodbhul8899]

with cte as ( select *,row_number() over(partition by cid) as rnk from flights ), cte2 as (select cid,min(rnk) as min_rnk,max(rnk) as max_rnk from cte group by 1) select c2.cid, max(case when c1.rnk=min_rnk then c1.origin end) as origin, max(case when c1.rnk=max_rnk then c1.destination end) as destination from cte2 c2 inner join cte c1 on c2.cid = c1.cid group by 1;

[@montudeb8060]

I love your content Ankit, i have one doubt here in problem 1, what about the customers who have direct flight, no changes in between, then the query doesn't give the ouput. Here is my solution and your feedback will be appreciated with cte as ( select *, lead(destination) over(partition by cid) as final_destination, count(fid) over(partition by cid) as flight_change from flights ) select cid, origin, case when final_destination is not null then final_destination when flight_change =1 then destination end as destination from cte where final_destination is not null or flight_change = 1;

[@vandanaK-mh9zo]

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 ;

[@srinidhimurali644]

for 2nd question: with cte as(select customer,order_date, row_number() over(partition by customer order by order_date) as rn from sales) select order_date, sum(rn) as new_customer_count from cte where rn = 1 group by order_date

[@rihanalli3754]

select cid,origin,dd as destination from( select*,lead(destination) over(partition by cid order by cid)as dd from flights) a where dd is not null

[@ank_kumar12]

ques 1- with cte as ( select *, lead(Destination) over(partition by cid) as final_destination from flights ) select cid,origin, final_destination from cte where (case when final_destination is not null then 1 else 0 end) = 1 ; ques 2- with cte as ( select customer,min(order_date) as order_date from sale group by customer ) select monthname(order_date) as month,count(customer) as new_customer_count from cte group by monthname(order_date);

[@ganeshmoorthy816]

Hi @ankitbansal6 . I am loving your content ❤.lets make the first question as more generic .when the customer has to board only one flight to reach his destination or have to board more than two flight to reach the destination, then we need to have the code like this. With flight_order as (select cid,origin, destination,row_number() over (partition by cid order by fid asc) as flight_order, lead(destination) over (partition by cod order by fid asc) as next_flight from flights), First_flight as (select * ,case when flight_order=1 then origin end as first_flight from flight_order), Last_flight as (select *,case when next_flight is null then destination end as last_flight from flight_order), Select o.cid, o.first_flight, d.last_flight from first_flight o inner join last_flight d on o.cid=d.cid where o.fist_flight is not null and d.last_flight is not null ;

[@kumarashirwadmishra7414]

I believe we can also try: WITH CFE AS( SELECT *, FIRST_VALUE(ORIGIN) OVER(PARTITION BY CID ORDER BY FID) AS ORIGIN_DESTINATION, LAST_VALUE(DESTINATION) OVER(PARTITION BY CID ORDER BY FID) AS FINAL_DESTINATION FROM FLIGHT) SELECT DISTINCT CID, ORIGINAL_DESTINATION, FINAL_DESTINATION FROM CFE;

[@hydergouri7826]

Great video..! Also bought your course, SQL zero to hero 🎉.

[@ankitbansal6]

Awesome! Thank you!

[@poonamwaghmare7205]

@@ankitbansal6How can I purchase that course please guide me🙏

[@ankitbansal6]

​@@poonamwaghmare7205 here you go www.namastesql.com/courses/SQL-For-Analytics-6301f405e4b0238f71788354

[@udhhavarora5483]

with base as (select date_format(order_date,'MMMM' ) as order_month,customer, rank() over(partition by customer order by order_date) as rnk from sales order by order_date) select order_month, sum(case when rnk=1 then 1 else 0 end) as count from base group by order_month

[@mohitmotwani9256]

For first questions, doing a lead helps with NextDesCTE as ( select *, lead(Destination) over (partition by cid order by cid, fid) as next_destination from flights ) select cid, fid, origin, next_destination as final_destination from nextDesCTe where next_destination is not null ;

[@ManpreetSingh-tv3rw]

Query 1 with cte1 as (select *, lead(origin,1) over (partition by cid order by fid) as transit, lead(destination,1) over (partition by cid order by fid) as finaldest from flights) select cid,origin,finaldest as destinationf from cte1 where destination=transit and finaldest is not null;

[@rahulmehla2014]

My approach for the 1st one: select distinct cid, first_value(origin) over(partition by cid order by fid ) as actual_origin, first_value(Destination) over(partition by cid order by fid desc) as final_destination from flights

[@aritrachatterjee8292]

Hi Sir, Below is another alternative one with cte as( Select *,lead(cid) over(partition by cid order by fid) as cid_nxt,lead(origin) over(partition by cid order by fid) as origin_nxt, lead(Destination) over(partition by cid order by fid) as Destination_nxt from flights) select cid,origin,Destination_nxt as Destination from cte where cid_nxt is not null;

[@PixelPioneerX132]

with cte as( select *,ROW_NUMBER() over(partition by cid order by fid) as rn , case when ROW_NUMBER() over(partition by cid order by fid) = 1 then origin end as origin1 , case when ROW_NUMBER() over(partition by cid order by fid) = 2 then destination end as destination1 from flights) select cid, max(origin1) as origin, max(destination1) as destination from cte group by cid;

[@sandipansarkar9211]

finished watching

[@invincible9971]

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);

[@rakeshkumarsharma3920]

solution 1 is wrong

[@manishkumartiwari420]

sol 2:- with cte as ( select month(order_date) as month, customer from sales ) select a.month,count( distinct a.customer) from cte as a left join cte as b on a.month>b.month and a.customer=b.customer where b.month is null group by a.month;

[@rihanalli3754]

with cte as( select *from( select *, row_number() over(partition by customer order by MONTH(order_date))as dd from sales)a where dd=1) select order_date,count(customer) as new_customer_count from cte group by order_date

[@priyanshshukla6224]

Hi Ankit Sir , One thing i noticed on the second question is that.. like while solving you said ..we dont want the second record from each window... SO what if we could have used FIRST_VALUE for each window , rather selecting throught rn = 1?

[@vlogsofsiriii]

@ankit bansal Can we implement the Problem 1 in this way ? SELECT DISTINCT cid,FIRST_VALUE(origin) over(partition by cid order by fid ASC) as origin , FIRST_VALUE(destination) over(partition by cid order by fid desc) as Dest from flights

[@user-sp3dr2vp6j]

with temp as ( select cid, origin, destination, RANK() OVER(PARTITION BY cid order by fid) rn1, RANK() OVER(PARTITION BY cid order by fid desc) rn2 from flights f ) select cid, max(case when rn1 = 1 then origin end) as origin, max(case when rn2 = 1 then destination end) as destination from temp group by cid

[@vishalsonawane.8905]

With CTE1 as( select *, ROW_NUMBER() over(partition by customer order by order_date) as rn from sales) select order_date, count(distinct customer) as count_new_cust from CTE1 where rn = 1 group by order_date;

[@user-he6cd7ov9p]

with cte as( select *, row_number () over (partition by customer order by order_date) as cnt from sales) select datepart(month,order_date) as mnt ,count(distinct customer) as new_cust_cnt from cte where cnt=1 group by datepart(month,order_date) order by mnt;

[@apna9656]

Hi Ankit My solution for 2 problem with cte as ( select Month, Distinct customer as customer_new from sales) select *, count(customer) over(partition by month order by customer_new) as cnk from cte

[@sandeepagarwal7155]

@ankit bansal select cid,origin,final_dest from ( select *, lead(destination,1) over(partition by cid order by fid) final_dest from flights) where final_dest is not null is this correct? for first solution , i think this is easy

[@rohithb65]

with cte1 as (select *, lead(Destination,1) over(partition by cid order by cid) as vv from flights) select cid,origin,vv from cte1 where vv is not null;

[@dhyeypatel1335]

This is temporary solution as we have only two sets of row for each CID.

[@aishwaryaramesh9343]

with cte as( select order_date,Customer, row_number() over(partition by customer order by order_date) as rn from sales ) select order_date,sum(rn) as total_new_customer from cte where rn=1 group by order_date

[@radhikagupta7314]

with sample as ( select customer,min(order_date) as min_date from sales group by customer) select distinct to_char(min_date,'month'),count(customer) from sample group by min_date

[@dd.c07]

For the first problem, I think we have to add "o.cid=d.cid" as well in the join condition.

[@santoshatyam1409]

I too think same here there is no match for other flights

[@piyushbamboriya1288]

Hello Ankit ji - For problem 2, if there is no new customer for a particular month then that month and it's count 0 won't be populated in output as we are filtering based on rn =1

[@ankitbansal6]

Right 👍

[@user-hf8tq2ul2q]

problem 2: with cte as (select order_date,customer,qty, row_number() over(partition by customer order by order_date) rnk from sales) select order_date,count(rnk) from cte where rnk=1 group by order_date

[@vevekkothapally]

correct me ,If I am wrong . As we are using row_number , Distinct is not necessary for dealing with the duplicate data of same customer visiting in same month as , fnc gives different value to duplicates. with cte as ( select *, row_number()over(partition by customer order by month(order_date)) as rnk from sales ) select month(order_date) , count(*) from cte where rnk =1 group by month(order_date);

[@ankitbansal6]

You are right

[@lol000333]

Second problem solution i feel this is better no need to use window WITH CTE AS( select customer, EXTRACT(YEAR_MONTH FROM min(order_date)) as first_visit from sales group by customer) SELECT first_visit,COUNT(CUSTOMER) AS NEW_CUSTOMER_COUNT FROM CTE GROUP BY first_visit

[@user-hf8tq2ul2q]

problem 1: with cte as (select cid,origin, lead(destination) over(partition by cid order by cid asc) f_destination from flights) select cid,origin,f_destination from cte where f_destination is not null

[@sahilummat8555]

;with cte as ( select * ,min(order_date)over(partition by customer order by order_date) as first_order_date from sales) select month(order_date), sum(case when order_date=first_order_date then 1 else 0 end ) as new_customer_count, STRING_AGG(customer,',') as cust from cte group by month(order_date)

[@user-xg6yi7hu1t]

Hi Ankit, For 1st Problem i tried with this approach with cte as (select cid,min(fid) min_f,max(fid) max_f from flights group by cid order by cid) select c.cid,f.origin,f1.destination from cte c join flights f on c.min_f=f.fid join flights f1 on c.max_f=f1.fid; 2nd problem approach with cte as (select customer,min(order_date) min_order_date from sales group by customer) select min_order_date,count(distinct customer) new_customer from cte group by min_order_date; let me know your thoughts on this?

[@PixelPioneerX132]

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

[@dakshbhatnagar]

My Solution to the problem:- with cte as ( select *, lead(destination) over() as EndDestination, row_number() over(partition by cid) as row_num from flights) select cid, origin, EndDestination from cte where row_num =1

[@Vaibha293]

select s.order_date,sum(case when order_date= minorder then 1 else 0 end) from sales s outer apply(select customer,min(order_date) minorder from sales where customer=s.customer group by customer)a group by s.order_date

[@khushboogupta4909]

For first solution , while joining we have put cid comparison also otherwise we will get wrong result if 2 cid has same connecting destination

[@tajbarsinghnegi1573]

What if there is more than one transition for one cid ? Then what will be changed in this query .

[@Apna_tahlka_123]

First view

[@Apna_tahlka_123]

Plj thoda thoda hindi me bhi explain krdia kro ise thoda understand krne me easy rehta h

[@dhyeypatel1335]

Problem 1 with cte as( select *, FIRST_VALUE(origin) over(partition by cid order by fid) as f1, FIRST_VALUE(destination) over(partition by cid order by fid desc) as f2 from flights) select cid,f1,f2 from cte group by cid,f1,f2 Problem 2 with cte as( select customer,min(order_date) as first_order_date from sales group by customer) select first_order_date,count(*) as new_customers from cte group by first_order_date

[@vishalmane3139]

konsa nashe krta h

[@dhyeypatel1335]

Sorry, I live in Gujarat.@@vishalmane3139

[@ayushi_patra]

1) select f1.cid, f1.origin, f2.destination from flights f1 join flights f2 on f1.cid = f2.cid and f1.destination = f2.origin ; 2) select substr(odate,1,7) as Month, count(customer) New_customer_count from (select customer, min(order_date) as odate from sales group by customer) a group by month ;

[@manjutharak]

Hi Brother make video on PL/SQL Object Type. In real-time I seen that object type they will in collection types and that collection type variable name they will mention as a datatype in procedure what purpose they will do Luke this my question

[@Blackhole-0]

Thanks much for videos. I have given interview and this helped a lot

[@ankitbansal6]

Wonderful!

[@jesseantony1223]

my solution: select cid,origin,last_destination from ( select *,lead(destination) over(partition by cid order by destination) as last_destination from flights) as r1 where last_destination is not null

[@ArjunV-wg2ex]

With A as (select customer, MIN(order_date) as first_visit from sales group by customer) select first_visit as monthh,count(customer) as cnt from A group by first_visit

[@vaibhavverma1340]

Hello Ankit Sir, Your Solution for problem 1 as per my understanding is not correct if we have three different use cases like Del -> Hyd , Hyd -> Blr, Blr -> Vns, please correct me If I am wrong. below is my sol for problem 1: with cte as (Select cid,origin,destination, lead(origin) over (partition by cid order by cid)next_origin, lag(origin) over (partition by cid order by cid)prev_origin from flights) select cid, max(case when prev_origin is null then origin end)origin, max(case when next_origin is null then destination end)destination from cte group by cid

[@rockingsurya4993]

with cte as ( select cid,origin, case when lead(origin) over(order by cid)=Destination then lead(Destination) over(order by cid) end as Destination from [ops-qaprodcarlsbad-5003].[flights] ) select * from cte where Destination IS NOT NULL

[@rockingsurya4993]

@AnkitBansal And for multiple entries like if there are n no of flights for one cid,then this would not work.SO i have coded that as:INSERT INTO [ops-qaprodcarlsbad-5003].[flights] (cid, fid, origin, Destination) VALUES ('1', 'f1', 'Del', 'Hyd'); INSERT INTO [ops-qaprodcarlsbad-5003].[flights] (cid, fid, origin, Destination) VALUES ('1', 'f2', 'Hyd', 'Blr'); INSERT INTO [ops-qaprodcarlsbad-5003].[flights] (cid, fid, origin, Destination) VALUES ('1', 'f3', 'Blr', 'Chennai'); INSERT INTO [ops-qaprodcarlsbad-5003].[flights] (cid, fid, origin, Destination) VALUES ('2', 'f4', 'Agra', 'Kol'); INSERT INTO [ops-qaprodcarlsbad-5003].[flights] (cid, fid, origin, Destination) VALUES ('2', 'f5', 'Kol', 'Kashmir'); WITH cte AS ( SELECT t1.cid, t1.origin, t2.destination,row_number() over(order by t1.cid) as rn_join FROM [ops-qaprodcarlsbad-5003].[flights] t1 INNER JOIN [ops-qaprodcarlsbad-5003].[flights] t2 ON t1.Destination = t2.origin ), cte2 as (SELECT *, LAST_VALUE(destination) OVER (PARTITION BY cid order by rn_join ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS new_Destination, ROW_NUMBER() OVER (PARTITION BY cid ORDER BY rn_join) AS rn FROM cte) SELECT cid,origin,new_Destination FROM cte2 where rn=1

[@anuragsrigyan677]

with cte as (select customer, min(order_date) as month from sales group by customer) select count(customer) as new_cust_count, to_char(month, 'Month') from cte group by month;

[@kartikpidurkar9590]

Problem 2: with cte as( select customer,min(order_date) as first_occ from sales group by customer) select first_occ, count(distinct customer) as New_cust from cte group by first_occ;

[@shailendra9292]

my PySpark solution for Q1: result_df = flights_df.groupBy("cid").agg( first("origin").alias("origin"), last("Destination").alias("Destination") )

[@ArunKumar-oi3tq]

FOR PROBLEM 1 - with cte1 as (SELECT * ,LEAD(DESTINATION)OVER(Partition by cid) FinalDestination from flights), cte2 as (select *, row_number () over(partition by cid) from cte1) select cid ,origin , FinalDestination from cte2 where row_number = 1 ;

[@namanjain3366]

select first_order_date, count(customer) from ( select customer, min(order_date) as first_order_date from sales group by customer ) A group by first_order_date

[@saurabhvasani7993]

select * from( select cid,origin,LEAD(Destination,1)OVER (partition by cid order by fid)Dest from flights )A WHERE Dest IS NOT NULL

[@sridineshr6598]

Hi @ankit for the first problem what if we had the input table like the below? 1 f1 Del Hyd 1 f2 Hyd Blr 2 f3 Mum Agra 2 f4 Agra Kol 1 f3 Blr Koc

[@iamsatyadeep]

@ankitbansal6 pls answer this

[@sumanguptha3823]

@Ankit: For prob 1, what if there is cid 3 with one row of data having origin and destination within one row. I have just begun the course yesterday. But would like to understand such situations as well. Thanks

[@adityakishan1]

Good question In such a case we can add a logic like below using UNION ALL where it will extract all those records for which the count of flight journey is 1 like below: select f1.cid, f1.origin, f2.Destination from flights f1 inner join flights f2 on f1.cid = f2.cid and f1.Destination = f2.origin union all select A.cid,max(A.origin),max(A.Destination) from flights A group by A.cid having count(A.cid) = 1

[@sumanguptha3823]

@@adityakishan1 Thanks Aditya

[@zaravind4293]

My approach for 1st problem below. Please let me know any suggestions with a as ( select f.*,dense_rank()over(partition by cid order by fid) rk from flights f ) select cid, max(case when rk =1 then origin end) origin, max(case when rk=2 then destination end) destination from a group by cid;

[@anchal7876]

with cte as (select cust_id, origin,destination,DENSE_RANK() over(partition by cust_id order by origin) as o, DENSE_RANK() over(partition by cust_id order by destination desc) as d from journey) SELECT cust_id, MAX(CASE WHEN o = 1 THEN origin END) AS 'origin', MAX(CASE WHEN d = 1 THEN destination END) AS 'destination' FROM cte GROUP BY cust_id;

[@user-lc8by2dy5x]

Can we do it in sql server - SELECT DISTINCT cid, FIRST_VALUE(origin) OVER (PARTITION BY cid order by cid ) AS origin, LAST_VALUE(Destination) OVER (PARTITION BY cid order by cid ) AS d_estination FROM flights;

[@roshangangurde7461]

Here is my solution for 2 nd problem with cte as (select * ,row_number() over(partition by customer order by order_date) as rn from sales) select date_format(order_date,"%b-%y") as Month , count(customer) as new_customer_count from cte where rn = 1 group by order_date;

[@ritudahiya6223]

@Ankit Bansal Please provide the credentials of website for practicing sql , I have already enrolled in your course

[@ankitbansal6]

Check the project section

[@RubeenaKhan-hx2mw]

What if the Destination and Origin are same but, the `cid` is different? Why are we not checking if the cid in first table is equal to the cid in the second table?

[@ankitbansal6]

We are doing that in join condition

[@testingsaiyaan9714]

Here is my Problem 2 solution: with cte as (select *, min(order_date) over( partition by customer ) as min_date from sales order by order_date, customer ) select order_date, count(customer) as new_cust_count from cte where order_date = min_date group by order_date

[@reshmaammu4726]

problem 1: with cte AS (select *, lead(origin) over(partition by cid order by cid) as dest, row_number() over(partition by cid order by cid) as flt_no from flight), cte2 AS(select a.cid, a.origin from cte a where dest is not null and flt_no=1), cte3 as (select a.cid, a.destination from cte a where dest is null) select origin, destination from cte2 a inner join cte3 b on a.cid=b.cid or with cte AS (select *, lead(origin) over(partition by cid order by cid) as dest from flights), cte2 AS(select a.cid, a.origin from cte a where dest is not null), cte3 as (select a.cid, a.destination from cte a where dest is null) select origin, destination from cte2 a inner join cte3 b on a.cid=b.cid

[@manishmr2116]

PROBLEM 2 - Simple solution WITHOUT using window functions !🔥🔥🔥 select first_purchase as date,count(distinct customer) as new_cutomer_count from( select customer,min(order_date) as first_purchase from sales group by customer) a group by first_purchase;

[@shinejohnson777]

select or_dt,count(customer) from( select customer,min(order_date) as or_dt from saless group by customer)x group by or_dt;

[@grzegorzko55]

--problem 1 select DISTINCT CID ,FIRST_VALUE(ORIGIN) OVER(PARTITION BY CID ORDER BY FID) AS test1 ,FIRST_VALUE(DESTINATION) OVER(PARTITION BY CID ORDER BY FID DESC) AS test2 FROM flights ORDER BY CID; --problem2 SELECT ORDER_DATE ,SUM(CASE WHEN rn = 1 THEN 1 ELSE 0 END ) AS cnt FROM( SELECT ORDER_DATE ,CUSTOMER ,QTY ,ROW_NUMBER() OVER(PARTITION BY CUSTOMER ORDER BY ORDER_DATE) AS rN FROM sales --WHERE ORDER_DATE IN('01-JAN-21', '01-FEB-21') ORDER BY ORDER_DATE ) GROUP BY ORDER_DATE ORDER BY ORDER_DATE;

[@vinodbhul8899]

with cte as (select order_date,customer from sales ) select c1.order_date,count(*) as cust_count from cte c1 left join cte c2 on c1.customer=c2.customer and c1.order_date>c2.order_date where c2.customer is null group by order_date;

[@shreyagupta8368]

for second solution ----------- select order_date, count(distinct customer) count_new_customer from (select customer, min(order_date) as order_date from sales group by customer) a group by order_date order by order_date;

[@PalakShah-fx2md]

@Ankit : Please review my solution for question:1 WITH RankedFlights AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY flight_id) AS origin_rank, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY flight_id DESC) AS destination_rank FROM flights ) SELECT customer_id, MAX(CASE WHEN origin_rank = 1 THEN origin_city END) AS origin_city, MAX(CASE WHEN destination_rank = 1 THEN destination_city END) AS destination_city FROM RankedFlights GROUP BY 1;

[@PalakShah-fx2md]

Solution for problem 2 WITH RankedSales AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS customer_rank FROM sales1 ) SELECT DATE_FORMAT(order_date, '%Y-%m') AS month_year, SUM(CASE WHEN customer_rank = 1 THEN 1 ELSE 0 END) AS new_customer_count FROM RankedSales GROUP BY 1;

[@shrutisugandhi1172]

solution for 2nd: select order_date,count(distinct customer) from ( select customer,order_date,row_number() over (partition by customer order by order_date) as rn from sales) where rn =1 group by order_date;

[@pavanakumarpk2525]

😂ye different solution hai kya

[@amalrajG]

simple Solution: select to_char(mnth,'MMMM-YY') as MonthN, count(customer) as cnt from (select customer, min(order_date) as mnth from sales group by customer) A group by mnth order by 1

[@user-zo5rg1md3k]

FIRST PROBLEM: select cid, max(case when fid = min_fid then origin else null end) as origin, max(case when fid = max_fid then destination else null end) as destination from ( select *, min(fid) over(partition by cid) as min_fid, max(fid) over(partition by cid) as max_fid from flights ) f group by f.cid; SECOND PROBLEM: select order_date, sum(case when nums = 1 then 1 else 0 end) as new_customer from (select *, row_number() over(partition by customer order by order_date) as nums from sales ) t group by order_date;

[@tastebuds6762]

Here's my solution to the probelm 1 that will solve two more scenarios listed below 1) If person board only one flight (direct flight from origin to destination) 2) If person board more than 2 flights ( for example 3 flights from origin to destination) Given below the updated insert script for the above scenarios 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'); INSERT INTO flights (cid, fid, origin, Destination) VALUES ('2', 'f5', 'Kol', 'Chen'); INSERT INTO flights (cid, fid, origin, Destination) VALUES ('3', 'f6', 'Chen', 'Blr'); Solution: with cte1 as( select cid,fid,origin,destination, row_number() over(partition by cid order by fid) as rn_o, row_number() over(partition by cid order by fid desc) as rn_d from flights ) ,cte2 as ( select c1.cid, case when c1.rn_o = 1 then c1.origin end as origin, case when c2.rn_d = 1 then c2.destination end as destination from cte1 c1 join cte1 c2 on c1.cid = c2.cid where c1.origin is not null and c2.destination is not null ) select * from cte2 where origin is not null and destination is not null;

[@arideepchakraborty5642]

Sir for the first problem if there are more than 2 rows, then this solution will not work. Can you please provide us a video of this same problem where there are more than 2 rows for each cid. Suppose 1 kol hyd 1 hyd bang 1 bang delhi Output 1 kol delhi

[@DanishAnsari-hw7so]

For problem 1, your query will not work in cases where there are more than 1 layovers in between (for example: Del -> Hyd -> Blr -> Pune). Sharing my generic generic query for handling such cases: select cid, max(case when flight_origin=1 then origin end) origin, max(case when dest=1 then destination end) destination from (select *, rank() over(partition by cid order by fid) flight_origin, rank() over(partition by cid order by fid desc) dest from flights) A group by cid;

[@rakeshkumarsharma3920]

Solution 1: using LEAD and LAG window function ----------------------------------------------------------------------------------- with t1 as ( select *, lag(Destination,1) OVER ( partition by cid ORDER BY cid) AS Destination1, lag(origin,1) OVER ( partition by cid ORDER BY cid) AS origin1 from flights ) SELECT cid,origin1,Destination from t1 where origin = Destination1;

[@insidehead]

FOR FRESHER ROLE?