SQL Data Analysis Interview Question #27/100 | SQL Challenge | SQL Tutorials | Business analyst

[@rishabhralli9151]

with cte as(
select * ,
row_number() over(partition by customer_id order by order_date desc) as new_rn
from orders)
select customer_id,
max(case
when new_rn=1 then order_amount
end) as latest_order,
max(case
when new_rn=2 then order_amount
end) as second_latest_order
from cte
group by 1;
could be done by more easy way

[@maheshchoure7310]

with temp as
(
select customer_id,order_amount ,row_number() over(partition by customer_id order by order_date desc) as rn
from orders
)
select customer_id , sum(case when rn = 1 then order_amount else 0 end) as latest_order_amount,
sum(case when rn = 2 then order_amount else 0 end) as second_latest_order_amount
from temp
group by customer_id

[@Savenature635]

here is my approach :
with cte as (select *,
dense_rank() over(partition by customer_id order by order_date desc) as rn
from az_orders)
select customer_id, max(case when rn=1 then order_amount end) as latest_order_amount,
min(case when rn=2 then order_amount end) as second_latest_order_amount
from cte
group by 1;

[@kailashpatro5768]

select customer_id , sum(case when rnk = 1 then order_amount end ) as lastest_amount ,
sum(case when rnk = 2 then order_amount end ) as second_lastest_amount
from (
select *, DENSE_RANK() over(partition by customer_id order by order_date desc) as rnk from amzorders) a
group by customer_id

[@Chathur732]

select customer_id, max(case when rn =1 then order_amount end) as latest_amount,
max(case when rn = 2 then order_amount end) as sec_latest_amount
from (
select *, row_number() over(partition by customer_id order by order_date desc) as rn
from orders)
group by customer_id
Hope this is a simple way.

[@lokeshladdha4520]

with CTES as
select cust_id ,
dense_rank(order_date)over(partition by cust_id desc) as RK
,order_amount
from orders
SELECT
cust_id,
MAX(CASE WHEN RK = 1 THEN order_amount END) AS Lastest_order_amount,
MAX(CASE WHEN RK = 2 THEN order_amount END) AS second_order_amount
FROM
CTES
GROUP BY
cust_id;

[@ChaitanyaKariya-x4q]

select customer_id ,
max(case when R_no = 1 then order_amount end) as latest_order_amount ,
max(case when R_no = 2 then order_amount end) as second_latest_amount
from
(select * ,
row_number() over (partition by customer_id order by order_date Desc) as R_no
from zero_analyst_amazon) as t2
group by customer_id

[@snehsparsh7954]

--Approach 2: Using CTEs with LEAD() Function
WITH order_stats AS (
SELECT
customer_id,
order_amount,
order_date,
LEAD(order_amount, 1) OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS second_latest_order_amount,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rnum
FROM
amazon_orders
)
SELECT
customer_id,
order_amount AS latest_order_amount,
second_latest_order_amount
FROM
order_stats
WHERE
rnum = 1;

[@santhoshkumars3967]

One more solution with self join
with cte as (select * , row_number() over (partition by customer_id order by order_date desc ) as rk
from orders )
select
c1.customer_id,
c1.order_amount as lastest_order_amount,
c2.order_amount as second_lastest_order_amount
from cte as c1 left join
cte as c2
on
c1.customer_id =c2.customer_id
where
c1.rk =1
and c2.rk =2

[@jatinjangir2293]

WITH CTE AS (
SELECT A.ORDER_AMOUNT,A.ORDER_ID,A.CUSTOMER_ID,ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY ORDER_DATE DESC)
AS RN
FROM amazon_orders A)
SELECT CUSTOMER_ID,MAX(case when rn = 1 then ORDER_AMOUNT end) as latest_order_amount,
MAX(case when rn = 2 then ORDER_AMOUNT end) as second_latest_order_amount
FROM CTE WHERE RN

[@snehsparsh7954]

-- Approach 4: Using ARRAY_AGG() Function
SELECT
customer_id,
ARRAY_AGG(order_amount ORDER BY order_date DESC)[OFFSET(0)] AS latest_order_amount,
ARRAY_AGG(order_amount ORDER BY order_date DESC)[OFFSET(1)] AS second_latest_order_amount
FROM
amazon_orders
GROUP BY
customer_id;

[@vinothkumars7421]

with cte as
(
SELECT *,row_number() over (Partition by customer_id order by order_date desc) as Rn
FROM q27_orders
)
SELECT
C1.CUSTOMER_ID,
C1.ORDER_AMOUNT AS LATEST_ORDER_AMN,
C2.ORDER_AMOUNT AS SECOND_LATEST_ORDER_AMNT
-- ,C1.RN
FROM
CTE C1
LEFT JOIN CTE C2 ON C1.CUSTOMER_ID = C2.CUSTOMER_ID
AND C1.RN = 1
AND C2.RN = 2 WHERE
C1.RN = 1

[@HARSHRAJ-gp6ve]

with cte as(
SELECT orders.*,DENSE_RANK()OVER(PARTITION BY customer_id ORDER BY order_date DESC) AS x1 FROM orders
),cte1 as(
select customer_id,order_amount as Latest_order_amount FROM cte where x1=1
),cte2 as(
select customer_id,order_amount as Second_latest_order_amount FROM cte where x1=2
)
select cte1.customer_id,Latest_order_amount,Second_latest_order_amount FROM cte1 JOIN cte2 ON cte1.customer_id=
cte2.customer_id;