All 4 questions are related to Window Functions, Most important topic for the interviews ✌ Btw, it's too coooold here to record a video🥶🥶 How's the temperature at your place??
At 22:03 Explanation should be in Dense Rank() we have 1,2,2,3 Rank that is no rank is skipped and for Rank() it would have been 1,2,2,4 that is one rank is skipped.
with cte as( select customer_id , case when first_value(order_date) over(partition by customer_id order by order_date) = customer_pref_delivery_date then 1 end as flag from delivery d ) select round(100. * count(flag)/count(distinct customer_id), 2) as immediate_percentage from cte
Hi Shashank, thank you so much for sharing us a broad knowledge of Data in all aspects. We want you to please make a separate video on which data science course is best to learn from scratch for a non technical guys to choose within all these competitive ed techs... Request you to share your views on this & help alot of data aspirants who's looking to start their career in data... Please do...
with cte as ( select , cust_id , order_date , del_date from delivery where (cust_id, del_id) = (select cust_id, min(del_id) from delivery group by 1)) , cte2 (select , a.cust_id , a.order_date , b.del_date from cte a left join cte b on a.cust_id = b.cust_id and a.order_date = b.del_date) select cast(count(a.cust_id)*100/count(*) as decimal(5,2)) immediate_percentage from cte2
Last prob: with total as( select count(distinct customer_id) as total_cust from delivery ) , total_joined as( select d.*,t.total_cust from total t inner join delivery d on (1=1) ) , first_orders as ( select * from ( select total_joined.*, rank() over(partition by customer_id order by order_date asc) rk from total_joined )q11 where rk=1 ) select round(count(distinct customer_id)::decimal /(select total_cust from first_orders limit 1)*100.0,2) as immediate_percentage from ( select * from first_orders where order_date=customer_pref_delivery_date )qqq
IMO, Simple answer is 'No'. Though with some hack, groupby+max would have still worked if every day had only one transaction for a max amount. But here the data shows there could be more than one transaction ID where max amount was billed , e.g. 29-04 , transaction-1,6 , amount-58. Even though we could use workaround, window functions are best to use here ( Rank transactions on basis of amount each day, select the number-1 rank transaction(s) everyday)
Answer-2 - Maximum Transaction Each Day (MySQL) SELECT transaction_id FROM ( -- rank the transaction on basis of amount, highest amount transaction in a day gets a rank-1 SELECT * , DENSE_RANK() OVER( PARTITION BY date(day) ORDER BY amount DESC) AS 'RANK' FROM Transactions ) AS transaction_ranked_data WHERE transaction_ranked_data.RANK=1 -- Filter only rank-1 transaction(s) from each day ORDER BY transaction_id;
with visit_info as (select user_id,visit_date first_visit,LEAD(visit_date,1,CAST('2021-1-1' as date)) OVER(PARTITION BY USER_ID ORDER BY visit_date) AS last_visit from Uservisits), rank as (select user_id,(last_visit-first_visit) as biggest_window, dense_rank() over(partition by user_id order by (last_visit-first_visit) desc) gap_rank from visit_info) select user_id,biggest_window from rank where gap_rank=1 attempted the first question as such :)
All 4 questions are related to Window Functions, Most important topic for the interviews ✌ Btw, it's too coooold here to record a video🥶🥶 How's the temperature at your place??
At 22:03
Explanation should be in Dense Rank() we have 1,2,2,3 Rank that is no rank is skipped
and for Rank() it would have been 1,2,2,4 that is one rank is skipped.
with cte as(
select
customer_id
, case when first_value(order_date)
over(partition by customer_id order by order_date)
= customer_pref_delivery_date then 1 end as flag
from delivery d
)
select
round(100. * count(flag)/count(distinct customer_id), 2) as immediate_percentage
from cte
Very informative. Keep making these type of videos.
🙏😊
Hi Shashank, thank you so much for sharing us a broad knowledge of Data in all aspects.
We want you to please make a separate video on which data science course is best to learn from scratch for a non technical guys to choose within all these competitive ed techs... Request you to share your views on this & help alot of data aspirants who's looking to start their career in data... Please do...
Hi , Thanks for your efforts to this video ... As always you make a good content !
Amzing shashank....Im unabl to submit 3rd problem.(department top three salary...) tried a lot with different ways....pls guide...Thanks
with cte as (
select
, cust_id
, order_date
, del_date
from delivery
where (cust_id, del_id) = (select cust_id, min(del_id) from delivery group by 1))
, cte2 (select
, a.cust_id
, a.order_date
, b.del_date
from cte a left join cte b on a.cust_id = b.cust_id and a.order_date = b.del_date)
select cast(count(a.cust_id)*100/count(*) as decimal(5,2)) immediate_percentage from cte2
Last prob:
with total as(
select count(distinct customer_id) as total_cust
from delivery
)
, total_joined as(
select d.*,t.total_cust
from total t inner join delivery d
on (1=1)
)
, first_orders as
(
select *
from
(
select total_joined.*,
rank() over(partition by customer_id order by order_date asc) rk
from total_joined
)q11
where rk=1
)
select round(count(distinct customer_id)::decimal
/(select total_cust from first_orders limit 1)*100.0,2) as immediate_percentage
from
(
select * from first_orders
where order_date=customer_pref_delivery_date
)qqq
Can we use group by and max() for Prob2 ?
IMO, Simple answer is 'No'. Though with some hack, groupby+max would have still worked if every day had only one transaction for a max amount. But here the data shows there could be more than one transaction ID where max amount was billed , e.g. 29-04 , transaction-1,6 , amount-58. Even though we could use workaround, window functions are best to use here ( Rank transactions on basis of amount each day, select the number-1 rank transaction(s) everyday)
#Script for Q2 - Maximum Transaction Each Day
CREATE TABLE Transactions
(
transaction_id INT,
day DATETIME,
amount INT
);
INSERT INTO Transactions
VALUES (8, '2021-4-3 15:57:28', 57),
(9, '2021-4-28 8:47:25', 21),
(1, '2021-4-29 13:28:30', 58),
(5, '2021-4-28 16:39:59', 40),
(6, '2021-4-29 23:39:28', 58);
SELECT * FROM Transactions;
Answer-2 - Maximum Transaction Each Day (MySQL)
SELECT transaction_id FROM
(
-- rank the transaction on basis of amount, highest amount transaction in a day gets a rank-1
SELECT * , DENSE_RANK() OVER( PARTITION BY date(day) ORDER BY amount DESC) AS 'RANK'
FROM Transactions
) AS transaction_ranked_data
WHERE transaction_ranked_data.RANK=1 -- Filter only rank-1 transaction(s) from each day
ORDER BY transaction_id;
with visit_info as
(select user_id,visit_date first_visit,LEAD(visit_date,1,CAST('2021-1-1' as date)) OVER(PARTITION BY USER_ID ORDER BY visit_date) AS last_visit
from Uservisits),
rank as (select user_id,(last_visit-first_visit) as biggest_window, dense_rank() over(partition by user_id order by (last_visit-first_visit) desc) gap_rank from visit_info)
select user_id,biggest_window from rank where gap_rank=1
attempted the first question as such :)