EY SQL INTERVIEW QUESTION | Daily Targets

[@kiranvarma5434]

Many thanks for increasing the zoom size of the video, it is clear now.

[@affanskm3530]

WITH recursive cte as (
SELECT StartDate as sdt, EndDate, Product,StartTarget, EndTarget FROM Targets
UNION
SELECT sdt + INTERVAL 1 DAY as dts, EndDate, Product, StartTarget, EndTarget FROM CTE
WHERE sdt < EndDate
),
cte1 as (
SELECT *,FIRST_VALUE(sdt) OVER(PARTITION BY EndDate,product ORDER BY product, sdt, EndDate ) as csdt FROM cte
)
SELECT Product,sdt as StartDate, EndDate,
StartTarget - (datediff(csdt, sdt)*(StartTarget - EndTarget)
/datediff(csdt,EndDate)) as DailyTarget
FROM cte1
ORDER BY product, sdt, EndDate