Capgemini SQL Interview Question - Find the percentage variance of sales from previous day

[@sravankumar1767]

Superb explanation 👌 👏 👍

[@CloudChallengers]

Thanks Sravan

[@sakshinaik05]

tysm for the videos plz bring #30 days sqlqueries challenge

[@bankimdas9517]

Thanks for sharing🙏

[@CloudChallengers]

You are welcome Bankim. Visit the SQL playlist for more such questions.

[@iamram436]

with cte as (select *,lag(sales,1,sales) over(order by dt) as lager from salesvar_tbl)
select *,((sales-lager)/lager)*100 as '%var' from cte where sales >= lager

[@CloudChallengers]

That works. Thanks for sharing simple query Ram.

[@RamanKumar-yb1dx]

In MS SQL
with cte as(
select *
, lag(sales, 1,0) over(order by dt) as prev_sales
from salesvar_tbl
)
select dt, sales,
((sales - prev_sales)/NULLIF((prev_sales), 0))*100 as "%var"
from cte
where sales > prev_sales;

[@CloudChallengers]

@RamanKumar-yb1dx, Thanks for sharing the alternative approach. Keep posting alternative approaches for upcoming videos as well.

[@Vaibha293]

Sir i have 1.5 year of experience as an SQL developer and I am looking to switch the company do you have any references or leads ..

[@CloudChallengers]

@Vaibha293, Create LinkedIn profile if you have not created yet and connect with at least 2k working professional. Look for the job that suits your skills and request for referral within your connections.
Also, I will share if any job openings in the organization I'm working.

[@Vaibha293]

@@CloudChallengers can you share your LinkedIn profile(URL) so that we could connect..

[@hairavyadav6579]

my answer
with cte as (select *,lag(sales) over(order by dt) pv from sales1)
select dt,sales,`%v` from (select*,ifnull(round((sales-pv)*100/pv,0),0) as `%v` from cte) sal where `%v` >=0;

[@theinsightminer08]

SELECT
*
FROM
(SELECT
dt,
sales,
COALESCE((sales - LAG(sales) OVER(ORDER BY dt)) *100 / LAG(sales) OVER(ORDER BY dt),0) as varpercent
FROM salesvar) AS subquery
wHERE varpercent >=0;

[@ShaileshJori]

Oracle
with rs as
(
SELECT dt,sales,((sales/lag(sales,1) over (order by dt))-1)*100 sale_per
from salesvar_tbl
)
select dt,sales,sale_per from rs
where sale_per>0 or sale_per is null;

[@masterhipster8358]

select dt, sales, var from (
select *
,lag(sales,1,0) over(order by dt) as v
,case when (sales - lag(sales) over(order by dt)) > 0 then round(((sales - lag(sales) over(order by dt))*100) / lag(sales) over(order by dt),2) end as var
from salesvar_tbl
) as key
where v < sales

[@saiteja-gb8ho]

WITH CTE AS
(SELECT *,(SALES -LAG(SALES)OVER(ORDER BY dt)) /LAG(SALES)OVER(ORDER BY DT)*100 AS "%var" FROM
SALESVAR_TBL)
SELECT DT,SALES,"%var" from cte
where "%var"0
or "%var" is null