Видео

Excellent

WITH CTE AS(SELECT *, EXTRACT(YEAR FROM sale_Date) AS YEAR, quarter(sale_Date) AS QUARTER FROM prd_sales),V1 AS( SELECT *, CONCAT(YEAR,'-','Q',QUARTER) AS QUARTERS FROM CTE),V2 AS( SELECT Product_id,Product_name,SUM(sale_qty) AS sale_qty,QUARTERS FROM V1 group by 1,2,QUARTERS),V3 AS( SELECT *, dense_rank()OVER(partition by QUARTERS ORDER BY sale_qty DESC) AS RNK FROM V2) SELECT * FROM V3 WHERE RNK = 1;

WITH cte AS ( SELECT product_id, product_name, sale_date, sale_qty, EXTRACT(YEAR FROM sale_date) AS year, CEIL(EXTRACT(MONTH FROM sale_date) / 3) AS quarter, SUM(sale_qty) AS total_sales_qty GROUP BY product_id, product_name, year, quarter ) cte1 AS ( SELECT product_id, product_name, sale_date, sale_qty, year, quantity, ROW_NUMBER() OVER (PARTITION BY year, quarter ORDER BY total_sales_qty DESC) AS rnk FROM cte ) SELECT Product_id, Product_name, year, quarter, total_sales_qty FROM cte WHERE rnk = 1 ORDER BY year, quarter;

with cte3 as (select distinct a.product_id,a.product_name,c.sale as sale_qty,concat(a.year_,'-Q',a.quarter) from( (select product_id,product_name,sale_date,Extract(Quarter from sale_date) as quarter ,Extract(Year from sale_date) as year_ from prd_sales) a join (with cte as (select product_id,product_name,Extract(Quarter from sale_date) as quarter ,Extract(Year from sale_date) as year_ ,sum(sale_qty) sale FROM prd_sales group by 1,2,3,4 order by 3,1), cte2 as (select *, rank() over(partition by quarter,year_ order by sale desc,year_) rn from cte) select * from cte2 where rn=1) c on c.quarter=a.quarter and c.year_=a.year_ and c.product_id=a.product_id and a.product_name=c.product_name)) select * from cte3

Very nicely explained

Thank you, very helpful, please make more trick question videos.

Datetime query is not working in oracle sql developer

select aa.* from landscape aa join landscape bb on aa.sourcename=bb.destination and aa.destination=bb.sourcename and aa.destination>bb.destination union select aa.* from landscape aa left join landscape bb on aa.sourcename=bb.destination and aa.destination=bb.sourcename where bb.sourcename is null

really helpful✌🤟

select * from distance where source_city < destination_city

Great video.

Hi Folks I have a different approach : WITH cte AS (select *, case when sale_Date between '2021-01-01' and '2021-03-01' then 'Q1-2021' when sale_Date between '2021-04-01' and '2021-06-01' then 'Q2-2021' when sale_Date between '2021-07-01' and '2021-09-01' then 'Q3-2021' when sale_Date between '2021-10-01' and '2021-12-01' then 'Q4-2021' when sale_Date between '2022-01-01' and '2022-03-01' then 'Q1-2022' end as "Quater" from prd_sales ),cte1 as (select *,sum(sale_qty) over (partition by Quater,Product_id ) Total from cte),cte2 as ( select Product_id,Product_name,Quater,sale_Date,max(Total) over (partition by Quater,Product_id) Top1 from cte1),cte3 as (select distinct Product_id,Product_name,Quater,Top1,DENSE_RANK() over (partition by Quater order by Top1 desc) rn from cte2) select * from cte3 where rn = 1 order by Quater

Thanks a lot Sir, for this tutorial. Need more videos like this.🙏

In case we don’t want to unload, and we just want to copy then, can we use ‘copy’ command to copy redshift data files into s3 ? @dailylifeofadataengineer7742

Why do you keep asking "OK?" and "Right?" like nobody is there to answer you buddy, you are having a delusion

Hi, you require a MIC. Please use 🙂

Lets Suppose if we want to find 10th consecutive number then writing multiple lead function is not good. Please provide a more generic solution

with cte as( Select *,count(product_qty)over(order by(select null) rows between unbounded preceding and 0 following) as grp from last_not_null) Select id,first_value(product_qty)over(partition by grp order by grp) as product_qty from cte

row_number is better but nice try sir

for cross jion nulls will be ingnored right ?

Thanks, great guidance.

Please more videos upload from windows function, CTE , store prodeure,

Upload more videos on windows function

Thank you for nice info, i am looking for same id 1 ad logged in again and logged out for second time and 3rd time, how to calculate each time, please help me

Then how to get only last quarter Total sales here

Thanks. That helped me a lot.😍

with data as (select distinct num, lead(num,1) over(order by num ) as x1, lead(num,2) over(order by num ) as x2 from Logs )select distinct num as ConsecutiveNums from data WHERE num=x1 AND x1 = x2;

Which SQL query is faster? Filter on Join criteria or Where clause? Please give the ans of above question.

Please focus on explaining clearly, and use the right example its very confusing, terms not used correctly

thank you it was really helpful. you have used three query regarding with regards to "unbounded preceding and unbounded following": if we use sum function instead of count and use "unbounded preceding and unbounded following" the output will be "55" if we use sum function instead of count and use "unbounded preceding and current row" the output will be "1, 3,6,....55" if we use sum function instead of count and use "current row and unbounded following" the output will be "55, 45, ....3,1" please correct me if my understanding is not correct

Very detailed. Helped me in one of the interviews. Thanks for sharing such sessions.

Hi .... Wat is the name of this SQL IDE ???

Informative problem and solution.

Hi Ankur, The videos are very informative. Could you please post the videos of the python questions you came across in your interviews? The SQL part is really great, if we can have python questions too, then it will add feather to the wings. It will level up the quality of content according to me. Thanks for taking time to read this comment/suggestion

WITH quarter_data as ( SELECT *, concat(year(sale_date),'-Q', datepart(quarter,sale_date)) as quarter from prd_sales ), ranked_data AS ( SELECT *, dense_rank() over(partition by quarter order by sale_qty DESC) AS drn ,sum(sale_qty) over(partition by quarter, product_id) AS total_quarter_sale FROM quarter_data ) SELECT product_id, product_name, total_quarter_sale, quarter FROM ranked_data WHERE drn = 1

Super

we can use NVL function like select avg(nvl(sale_amt,0)) as avg_sal from product_sales

😅

Bring mmore.

Nice approach❤. If first_value or last_values is not support in sql version then do you have any other approach? Please share.

thankyou so much for the video

Nice vide with perfect explanation.

Nicely explained Ankit.

thanks. That helped me a lot.

Wow! Ankit you are taking unique cases to solve in SQL and this will definitely helps to upgrade the SQL concepts Thanks man👍

thanks sir 🙏

Create a video duplicate records with timestamp

How to calculate the working hours excluding the non working hours

select * from distance where source_city < destination_city;

Select * from distance where source_city in ('Mumbai','Delhi', 'Bangalore') and destination_city in ('Pune', 'Kolkata');