we can also achive this by using following : with cte as ( select *, LEAD(total_sales_revenue,1) over (partition by product_id order by year) as nxt_rev, LEAD(total_sales_revenue,2) over (partition by product_id order by year) as nxtnext_rev from sales) select p.product_id,p.product_name,p.category from cte c join products p on c.product_id=p.product_id where nxtnext_rev>nxt_rev and nxt_rev>total_sales_revenue
with cte as (select *,(lead(total_sales_revenue) over (partition by product_id order by year)-total_sales_revenue)as x from sales) select distinct c.product_id,p.product_name from cte c inner join productss p on c.product_id=p.product_id where c.product_id not in (select product_id from cte where x
My solution with cte as ( SELECT * ,LEAD(total_sales1_revenue,1) OVER(PARTITION BY product_id ORDER BY year) Year1 ,LEAD(total_sales1_revenue,2) OVER(PARTITION BY product_id ORDER BY year) year2 ,ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY year) as RW FROM sales1 ) ,cte2 as( SELECT *, CASE WHEN (total_sales1_revenue < Year1 ) AND (Year1 < Year2) THEN 1 ELSE 0 END flag FROM cte WHERE RW = 1 ) SELECT P.* FROM products1 P JOIN cte2 C ON P.product_id=C.product_id WHERE C.flag = 1
please find the below solution : with cte as(select a.product_id as pid,a.PRODUCT_NAME as pn,a.CATEGORY as c, case when (lead(b.TOTAL_SALES_REVENUE,1,0) over (order by a.product_id) > b.TOTAL_SALES_REVENUE and lead(b.TOTAL_SALES_REVENUE,2,0) over (order by a.product_id) > lead(b.TOTAL_SALES_REVENUE,1,0) over (order by a.product_id)) then a.product_id else null end as x from products a inner join sales b on a.product_id = b.product_id) select pid as product_id,pn as product_name,c as category from cte where x is not null;
Hi bro, can you please send me the solution for this question create table brands ( Year int, Brand varchar(20), Amount int ) insert into brands values (2018, 'Apple', 45000); insert into brands values (2019, 'Apple', 35000); insert into brands values (2020, 'Apple', 75000); insert into brands values (2018, 'Samsung', 15000); insert into brands values (2019, 'Samsung', 20000); insert into brands values (2020, 'Samsung', 25000); insert into brands values (2018, 'Nokia', 21000); insert into brands values (2019, 'Nokia', 17000); insert into brands values (2020, 'Nokia', 14000); Write a query to fetch the records of brands whose Revenue is increasing every year? Altough the question you have solved is similar. But differrence is it is a single table and don't have id. I tried to solve the approach you have said. But I can't resolve it. Please make a video or help me in the solution..
HI @chandan , Please find the below solution : with cte as(select a.brand as bnd, case when (lead(a.amount,1,0) over (order by null) > a.amount and lead(a.amount,2,0) over (order by null) > lead(a.amount,1,0) over (order by null)) then a.year else null end as x from brands a) select bnd from cte where x is not null; the outout is samsung
Thanks for the video. I used the lag function and used distinct instead of max in the output line. Is it valid? Please check. with cte as ( select p.product_id, p.product_name, s.year, total_sales_revenue, LAG(total_sales_revenue,1) over (partition by p.product_id order by year) as prev_year_revenue from products p join sales s on p.product_id = s.product_id --order by p.product_id, s.year ) select distinct product_id, product_name from cte where product_id not in (select product_id from cte where total_sales_revenue < prev_year_revenue)
my solution on MSSQL DB: with cte as ( Select * ,case when total_sales_revenue< lead(total_sales_revenue,1, total_sales_revenue+1)over(partition by product_id order by year) then 1 else null end as new from sales ), sales_cte as( Select * from sales where product_id not in (select product_id from cte where new is null) ) select products.* from sales_cte inner join products on sales_cte.product_id = products.product_id group by products.product_id, products.product_name, products.category
select * from products where product_id not in ( select distinct product_id from ( select *, coalesce(total_sales_revenue-lag(total_sales_revenue) over(partition by product_id order by year asc),0) as lag_col from sales)a where a.lag_col
bro why don't you explain first logic in excel then jump to sql editor to write. can you explain the logic behind total_sales_revenue>nxt>rvn in excel. because there are for product_id 2 every row doesn't satisfy the codition. but how it is working I didn't get it. Please explain in excel first. I have already given feedback related to you previously.
It should not satisfy the condition, i am selecting those ids that are not satisfying the condition caz total_sales_revenue>nxt_rvn means next year revenue is less which goes against the condition in the question, so we will not consider those ids hence i have used "not in" in where clause. I hope it's clear now. and i will try to first show in excel the move to editor in coming videos.
we can also achive this by using following :
with cte as (
select *,
LEAD(total_sales_revenue,1) over (partition by product_id order by year) as nxt_rev,
LEAD(total_sales_revenue,2) over (partition by product_id order by year) as nxtnext_rev
from sales)
select p.product_id,p.product_name,p.category from cte c
join products p
on c.product_id=p.product_id
where nxtnext_rev>nxt_rev and nxt_rev>total_sales_revenue
with cte as(
select *,iif(lag(total_sales_revenue,1,total_sales_revenue)over(Partition by product_id order by year)
with cte as (select *,(lead(total_sales_revenue) over (partition by product_id order by year)-total_sales_revenue)as x from sales)
select distinct c.product_id,p.product_name from cte c inner join productss p on c.product_id=p.product_id where c.product_id not in (select product_id from cte where x
My solution
with cte as (
SELECT *
,LEAD(total_sales1_revenue,1) OVER(PARTITION BY product_id ORDER BY year) Year1
,LEAD(total_sales1_revenue,2) OVER(PARTITION BY product_id ORDER BY year) year2
,ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY year) as RW
FROM sales1
)
,cte2 as(
SELECT *,
CASE WHEN (total_sales1_revenue < Year1 ) AND (Year1 < Year2) THEN 1 ELSE 0 END flag
FROM cte
WHERE RW = 1
)
SELECT P.*
FROM products1 P
JOIN cte2 C ON
P.product_id=C.product_id
WHERE C.flag = 1
please find the below solution :
with cte as(select a.product_id as pid,a.PRODUCT_NAME as pn,a.CATEGORY as c,
case when (lead(b.TOTAL_SALES_REVENUE,1,0) over (order by a.product_id) > b.TOTAL_SALES_REVENUE
and lead(b.TOTAL_SALES_REVENUE,2,0) over (order by a.product_id) > lead(b.TOTAL_SALES_REVENUE,1,0) over (order by a.product_id)) then a.product_id else null end as x
from products a inner join sales b on a.product_id = b.product_id)
select pid as product_id,pn as product_name,c as category from cte where x is not null;
can we do it without using a CTE? i suspect we can , though unlikely to be optimal, thoughts??
Try it out
Hi bro,
can you please send me the solution for this question
create table brands
(
Year int,
Brand varchar(20),
Amount int
)
insert into brands values (2018, 'Apple', 45000);
insert into brands values (2019, 'Apple', 35000);
insert into brands values (2020, 'Apple', 75000);
insert into brands values (2018, 'Samsung', 15000);
insert into brands values (2019, 'Samsung', 20000);
insert into brands values (2020, 'Samsung', 25000);
insert into brands values (2018, 'Nokia', 21000);
insert into brands values (2019, 'Nokia', 17000);
insert into brands values (2020, 'Nokia', 14000);
Write a query to fetch the records of brands whose Revenue is increasing every year?
Altough the question you have solved is similar. But differrence is it is a single table and don't have id.
I tried to solve the approach you have said. But I can't resolve it. Please make a video or help me in the solution..
ok will make a video on this soon
HI @chandan , Please find the below solution :
with cte as(select a.brand as bnd,
case when (lead(a.amount,1,0) over (order by null) > a.amount
and lead(a.amount,2,0) over (order by null) > lead(a.amount,1,0) over (order by null)) then a.year else null end as x
from brands a)
select bnd from cte where x is not null;
the outout is samsung
Thanks for the video.
I used the lag function and used distinct instead of max in the output line. Is it valid? Please check.
with cte as (
select p.product_id, p.product_name,
s.year,
total_sales_revenue,
LAG(total_sales_revenue,1) over (partition by
p.product_id order by year) as prev_year_revenue
from products p
join sales s on p.product_id = s.product_id
--order by p.product_id, s.year
)
select distinct product_id, product_name
from cte
where product_id not in (select product_id
from cte
where total_sales_revenue < prev_year_revenue)
First view
my solution on MSSQL DB:
with cte as
(
Select *
,case when total_sales_revenue< lead(total_sales_revenue,1, total_sales_revenue+1)over(partition by product_id order by year) then 1 else null end as new
from sales
), sales_cte as(
Select * from sales where product_id not in (select product_id from cte where new is null)
)
select products.* from sales_cte inner join products on sales_cte.product_id = products.product_id
group by products.product_id, products.product_name, products.category
select * from products where product_id not in (
select distinct product_id from
(
select *,
coalesce(total_sales_revenue-lag(total_sales_revenue) over(partition by product_id order by year asc),0) as lag_col
from sales)a
where a.lag_col
bro why don't you explain first logic in excel then jump to sql editor to write. can you explain the logic behind total_sales_revenue>nxt>rvn in excel. because there are for product_id 2 every row doesn't satisfy the codition. but how it is working I didn't get it. Please explain in excel first. I have already given feedback related to you previously.
It should not satisfy the condition, i am selecting those ids that are not satisfying the condition caz total_sales_revenue>nxt_rvn means next year revenue is less which goes against the condition in the question, so we will not consider those ids hence i have used "not in" in where clause.
I hope it's clear now. and i will try to first show in excel the move to editor in coming videos.