DELOITTE SQL Interview Question | SQL Intermediate Question 19

Поделиться
HTML-код
  • Опубликовано: 22 ноя 2024

Комментарии • 17

  • @ardsha
    @ardsha 8 месяцев назад +2

    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

  • @gurupradeep9648
    @gurupradeep9648 4 месяца назад

    with cte as(
    select *,iif(lag(total_sales_revenue,1,total_sales_revenue)over(Partition by product_id order by year)

  • @shryk0s963
    @shryk0s963 6 месяцев назад

    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

  • @anime_763
    @anime_763 6 месяцев назад

    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

  • @jatinjangir2293
    @jatinjangir2293 2 месяца назад

    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;

  • @suvadipkundu152
    @suvadipkundu152 6 месяцев назад

    can we do it without using a CTE? i suspect we can , though unlikely to be optimal, thoughts??

    • @Code-Con
      @Code-Con  6 месяцев назад

      Try it out

  • @chandanpatra1053
    @chandanpatra1053 8 месяцев назад

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

    • @Code-Con
      @Code-Con  8 месяцев назад

      ok will make a video on this soon

    • @jatinjangir2293
      @jatinjangir2293 2 месяца назад

      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

  • @wardaddy3
    @wardaddy3 5 месяцев назад

    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)

  • @saicharan5846
    @saicharan5846 8 месяцев назад

    First view

  • @vijaygupta7059
    @vijaygupta7059 7 месяцев назад

    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

  • @shyamshivakumar7807
    @shyamshivakumar7807 4 месяца назад

    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

  • @chandanpatra1053
    @chandanpatra1053 8 месяцев назад

    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.

    • @Code-Con
      @Code-Con  8 месяцев назад

      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.