วีดีโอ

Kindly provide create and insert statements ?

DENSE_RANK would be the most appropriate here. There wouldn't be a rank=2 if there are two orders with the same order value which is highest within a segment.

with cte as( select seat_number,occupancy, sum(occupancy) over ( partition by substr(seat_number,1,1) rows between 3 preceding and current row)as rnk from cinema_tickets ) select seat_number from cte where rnk=0 and right(seat_number,1) not in(1,2,3); simple answer

Expedia SQL Interview Question: Find all possible unique routes in the month of January

with cte as( select DISTINCT(DATE(Order_timestamp)) as time_stamp1 FROM Orders where DAYOFWEEK(order_timestamp)=2 ),cte2 as( SELECT time_stamp1, COALESCE( LEAD(time_stamp1) OVER (ORDER BY time_stamp1), ADDDATE(time_stamp1, 7) ) AS time_stamp2 FROM cte ),cte3 as( SELECT cte2.time_stamp1, COUNT(*) AS total_orders, COUNT(CASE WHEN Orders.is_defective = TRUE THEN 1 ELSE NULL END) AS x1, COUNT(CASE WHEN Orders.is_completed = TRUE THEN 1 ELSE NULL END) AS x2 FROM cte2 JOIN Orders ON Orders.order_timestamp BETWEEN cte2.time_stamp1 AND cte2.time_stamp2 GROUP BY cte2.time_stamp1 ) select time_stamp1,(x1/total_orders) as deffective_rate,(x2/total_orders) as effective_rate FROM cte3; Good question

Very good question

Mujse kyu ni bana😢

I always prefer cte

Bhot hard

Great explanation!f Kindly increase Screen resolution from next vid onwards or zoom in a little bit

Nice question

Thanks for sharing the Question & Solution. Your explanation is crystal clear. Keep it up. 😊👍

not to sound as creep but this is one of the most soothing voices I ever heard , please keep making such tutorial videos 🙂

Okay to clarify it's inner join, the distinction between cross join and inner is that in cross join we get cartesian product whereas in inner join it's primarily intersection of records. Besides when two tables have same columns - we can skip ON keyword and use USING keyword. The play of keywords doesn't decide the type of join we are implementing

Somehow the example you've taken has made the concept of Joins in SQL much more complicated (I would stick with categorical examples). I believe you've showed an example of Cross Join rather than INNER Join buy not using an ON clause.

Shouldn't the syntax be "INTERVAL '6' DAY " instead of "INTERVAL '6 days' "?

with cte as (select *, lag(occupancy,1) over(partition by substring(seat_number, 1, 1)) as prev_occ, lag(occupancy, 2) over(partition by substring(seat_number, 1, 1)) as prev_occ2, lag(occupancy, 3) over(partition by substring(seat_number, 1, 1)) as prev_occ3 from cinema_tickets) select concat(start_seat, '-', seat_number) as vacant from (select concat(substring(seat_number, 1, 1), substring(seat_number, 2, 2) - 3) as start_seat, seat_number from cte where occupancy = 0 and prev_occ = 0 and prev_occ2 = 0 and prev_occ3 = 0) a

Could you please share the table creation commands in description box to practice these questions

the last error was due to order of execution. You can't use alias name in where as "select" statement run after "where" clause. . . . . . . Btw your voice is ❤.

SELECT concat(seat,' to ',concat(left(seat,1),right(seat,1)+3)) as avaibable_seats FROM ( SELECT seat, ns1, ns2, ns3, ns4 FROM ( SELECT seat, occupancy AS ns1, LEAD(occupancy, 1) OVER(PARTITION BY LEFT(seat, 1) ORDER BY seat) AS ns2, LEAD(occupancy, 2) OVER(PARTITION BY LEFT(seat, 1) ORDER BY seat) AS ns3, LEAD(occupancy, 3) OVER(PARTITION BY LEFT(seat, 1) ORDER BY seat) AS ns4 FROM cinema_tickets ) AS s WHERE ns1 = 0 AND ns2 = 0 AND ns3 = 0 AND ns4 = 0 ) AS consecutive_empty_groups;

The question says unique users so doesn't that contradict the solutions ?, let me know if i am wrong

Hey I'm an experienced machine learning engineer and I really appreciate what you're doing, keep going. All the best for your career.

Please solve some joins hard question

Recommend at the right time preparing for a data analyst interview

Thank you for the video😊. Looking forward for more. A small request/suggestion the audio is barely audible.

with cte as( SELECT *,cast(SUBSTRING(seat_number, 2, LEN(seat_number) - 1) as int) as sn, SUBSTRING(seat_number,1,1) as seat FROM cinema_tickets ),cte2 as( select *, lead(occupancy,1) over (partition by seat order by sn) as l1, lead(occupancy,2) over (partition by seat order by sn) as l2, lead(occupancy,3) over (partition by seat order by sn) as l3 from cte ) select seat_number,CONCAT(seat,sn+3) as succestive_next_seat from cte2 where occupancy+l1+l2+l3 = 0

If any one is looking out purely based on CTE's ;WITH CTE AS (SELECT * FROM cinema_tickets WHERE occupancy = 0 ), CTE2 AS (SELECT seat_number AS seat_number, occupancy AS occupancy, CAST(SUBSTRING(SEAT_NUMBER, 2, 2) AS INT) - (ROW_NUMBER() OVER (PARTITION BY SUBSTRING(SEAT_NUMBER, 1, 1) ORDER BY CAST(SUBSTRING(SEAT_NUMBER, 2, 2) AS INT) ) ) AS RN FROM CTE ), CTE3 AS (SELECT SUBSTRING(seat_number, 1, 1) AS SUB, RN AS RN, COUNT(RN) AS C FROM CTE2 GROUP BY SUBSTRING(seat_number, 1, 1), RN HAVING COUNT(RN) = 4 ), CTE4 AS (SELECT SUBSTRING(CTE2.SEAT_NUMBER, 1, 1) AS SEAT_ROW, CAST(SUBSTRING(CTE2.seat_number, 2, 2) AS int) AS SEAT_NUM FROM CTE3 INNER JOIN CTE2 ON SUBSTRING(CTE2.SEAT_NUMBER, 1, 1) = CTE3.SUB AND CTE2.RN = CTE3.RN WHERE CTE3.C = 4 ) SELECT SEAT_ROW, CONCAT(SEAT_ROW, CAST(MIN(SEAT_NUM) AS varchar(5))) AS MIN_SEAT, CONCAT(SEAT_ROW, CAST(MAX(SEAT_NUM) AS varchar(5))) AS MAX_SEAT FROM CTE4 GROUP BY SEAT_ROW

The reason why she didn't get answer while using tab1 is you cannot use alias names along with where or having because according to order of execution Select statement will be executed after where/having is executed. So it do not recognize the new name that is assigned in select statement (which is not executed btw) . The approach of using tab2 is brilliant. Or you can directly use occupancy+s1+s2+s3 in where statement when using tab1. Just wanted to share.

Can i get pdf of this questions

How do we write if sales region max - each sales point of particular region? Example shop 1 revenue and total of all sales maximum of particular region?

Thanks for posting DS content. Should one start from first video or are these random

Would you please tell me what do you refer to research on DS?

with cte as (select cust_id,txn_date,cumm_tax, rank() over(order by cumm_tax) as rnk from (SELECT cust_id,txn_date,sum(txn_amt) over(partition by cust_id order by txn_date) as cumm_tax FROM customer_txn ) x where cumm_tax >=2000 ) select * from cte order by cust_id limit 1

You are just like me before solving this i did the same mistake

Different approach: with tab1 as( Select *, sum(occupancy) over(partition by substring(seat_number, 1,1) order by cast(substring(seat_number,2,len(seat_number)) as int) rows between current row and 3 following) as sum_4_prec, lead(seat_number,3) over(partition by substring(seat_number, 1,1) order by cast(substring(seat_number,2,len(seat_number)) as int)) as end_seat from cinema_tickets ) Select seat_number, end_seat from tab1 where end_seat is not null and sum_4_prec = 0

Engaging stuff!

Very innovative animation, great job👍 explained very well.

Informative video!

Great stuff

Was this asked in initial hackerrank test?

with cte as ( select *, sum(txn_amt) over(partition by cust_id order by txn_date ) as rolling_sum from customer_txn ) select cust_id,min(txn_date) as dates,min(rolling_sum) as rolling_sum from cte where rolling_sum>2000 group by cust_id this should give the same result

Good

Unclear problem statement

with cte1 as (select *,sum(txn_amt) over(partition by cust_id order by cust_id,txn_date,txn_amt) as rs from customer_txn), cte2 as (select *,rank() over(partition by cust_id order by txn_date) as rn from cte1 where rs>=2000) select cte2.cust_id,cte2.txn_date from cte2 where rn=1;

Can you share schema if you have?

Simple solution : Select * from (select case when A.occupancy+A.s1+A.s2+A.s3 = 0 then A.seat_number end as start_seat, case when A.occupancy+A.s1+A.s2+A.s3 = 0 then lead(A.seat_number,3) over() end as end_seat from (select seat_number,occupancy, lead(occupancy,1) over (partition by substr(seat_number,1,1)) s1, lead(occupancy,2) over(partition by substr(seat_number,1,1)) s2, lead(occupancy,3) over(partition by substr(seat_number,1,1)) s3 from cinema_ticket) A) B where B.start_seat is not null and B.end_seat is not null

My Solution with cte as( select cust_id, txn_date, running_total from( select cust_id, txn_date,txn_amt, sum(txn_amt) over(partition by cust_id order by txn_date rows between UNBOUNDED PRECEDING AND CURRENT ROW ) as running_total from customer_txn)temp1 where running_total >= 2000) select cust_id, min(txn_date) as date, min(running_total) as sum from cte group by cust_id

I like how raw this video is......and you also keep getting stuck at some points........as a newbie we always make mistakes and get confused.... you can try preparing well before recording videos.... it will give you more time to explain things better

with tab1 as (select *, row_number() over(partition by t5.cust_id) as ranks from (select * from (select cust_id,txn_date, sum(txn_amt) as amt, sum(sum(txn_amt)) over(partition by cust_id rows between unbounded preceding and current row) as c_sum from customer_txn group by cust_id,txn_date order by cust_id,txn_date) t where t.c_sum >= 2000) t5) select cust_id,txn_date,c_sum from tab1 where ranks = 1;