Infosys SQL Interview Question| First & Last order of each customer #dataanalytics #sql

In this video, we will solve the SQL Medium Level interview question asked in Infosys
SQL Script:
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
order_amount DECIMAL(10,2)
);

INSERT INTO Orders (order_id, customer_id, order_date, order_amount) VALUES
(101, 1, '2024-01-05', 500.00),
(102, 2, '2024-01-07', 700.00),
(103, 1, '2024-02-10', 450.00),
(104, 3, '2024-02-15', 900.00),
(105, 2, '2024-03-20', 650.00),
(106, 1, '2024-03-25', 800.00),
(107, 3, '2024-04-05', 1200.00),
(108, 2, '2024-04-10', 750.00),
(109, 3, '2024-05-01', 1000.00);

Easy - Level questions -

Medium - Level questions -

Hard-Level SQL interview questions -

If you have any interesting SQL questions, feel free to send them to me at

#sqlserver #dataanalytics #dataanalyst
#sqlqueries
#education #data #sqlinterviewquestionsandanswers
#faangcodinginterviewpreparation
#infosys

[prashantbansode]

with cte as (
select *,
ROW_NUMBER() over (partition by customer_id order by order_date ) as firstorderrnk,
ROW_NUMBER() over (partition by customer_id order by order_date desc) as lastorderrnk
from orders
) select customer_id,
max(case when firstorderrnk =1 then order_date end) as firstorder,
max(case when lastorderrnk = 1 then order_date end )as lastorder
from cte
where firstorderrnk ='1' or lastorderrnk ='1'
group by customer_id

[naveenbm]

very nice, great effort and please post more like on SQL related interview asked ones
thanks

[saijaswanth]

WITH first_order_last_order AS (
SELECT*,
DENSE_RANK() OVER(PARTITION BY customer_id ORDER BY order_date ASC) AS first_order,
DENSE_RANK()OVER(PARTITION BY customer_id ORDER BY order_date DESC) AS last_order
FROM orders_)

SELECT order_id, customer_id, order_date, order_amount,
CASE WHEN first_order = 1 THEN 'First Order'
WHEN last_order = 1 THEN 'Last Order'
END AS Order_type
FROM first_order_last_order
WHERE first_order = 1 OR last_order = 1;