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