🧠 Day 2 – 10 Business SQL Questions
📊 Tables & Sample Data
1️⃣customers
customer_id full_name city signup_date
1 Alice Johnson London 2023-01-10
2 Bob Smith Manchester 2023-02-15
3 Carol White Birmingham 2023-03-05
4 David Brown London 2023-03-20
5 Emma Davis Leeds 2023-04-01
2️⃣orders
order_id customer_id order_date total_amount
101 1 2023-05-01 120
102 2 2023-05-03 80
103 1 2023-05-10 150
104 3 2023-05-12 200
105 4 2023-05-15 50
106 2 2023-05-20 75
107 5 2023-05-22 300
3️⃣order_items
order_id product_name quantity price
101 Laptop 1 120
102 Mouse 2 40
103 Phone 1 150
104 Monitor 2 100
105 Keyboard 1 50
106 Mouse 1 75
107 Laptop 2 150
1️⃣Rank Customers by Total Spend
Rank customers based on total revenue (highest = rank 1).
👉 Use RANK().
With cte1 as (Select customer_id,sum(total_amount) as tot_Rev
From orders
Group by customer_id),
Select customer_id,tot_rev, rank()over (order by tot_rev desc) as rk
from cte1 ;
2️⃣Dense Rank Customers by Total Spend
Same as above but use DENSE_RANK().
With cte1 as (Select customer_id,sum(total_amount) as tot_Rev
From orders
Group by customer_id),
Select customer_id,tot_rev, dense_rank()over (order by tot_rev desc) as rk
from cte1 ;
👉 Explain difference in result.
The ties rank will not be skipped in dense rank where as in rank it will skip the ties
3️⃣Running Revenue Over Time
Calculate cumulative company revenue ordered by order_date.
Select sum(total_Amount) over(order by order_date) as cumulative_rev
From orders;
👉 Use SUM() OVER (ORDER BY order_date).
4️⃣Customer Lifetime Value (CLV)
For each customer, show:
total revenue
number of orders
average order value
All in one query.
Select customer_id,sum(total_amount) as total_revenue,
Count(order_id) as no_of_orders,
Avg(total_amount) as avg_order_value
From orders
Group by customer_id ;
5️⃣First Order Per Customer
Find the first order date for each customer.
👉 Use ROW_NUMBER().
With cte1 as
Select customer_id,order_date ,row_number()over(order by order_Date) as rn from
orders )
Select * from cte1 where rn = 1
6️⃣Latest Order Per Customer
Find the most recent order for each customer.
With ct1 as
(Select customer_id,order_id,row_number()over(partition by customer_id order by order_Date
desc) as rn
From orders)
Select * from ct1 where rn = 1;
7️⃣Identify Repeat Customers
Return customers who placed more than 1 order
But also show:
first order date
last order date
total spent
with ct1 as (
select customer_id,count(order_id)
from orders group by customer_id
having count(order_id) > 1),
ct2 as (
select customer_id,max(order_date) as last_order_date,min(order_date) as first_order_date,
sum(total_amount) as total_spent
from orders group by customer_id )
select customer_id,first_order_date,last_order_date,total_spent from ct2 where customer_id in
(select customer_id from ct1)
8️⃣Percentage Contribution to Total Revenue
For each customer, calculate:
customer_revenue / total_company_revenue * 100
👉 Use window function instead of subquery.
With ct1 as
Select customer_id,sum(total_amount) over (partition by customer_id) as
total_revenue_customer,
SUM(total_amount) OVER() as grand_total_rev,
From orders)
Select customer_id, (total_revenue_customer/ grand_total_rev)*100 as
percent_contribution
From ct1;
9️⃣Top 2 Orders Per Customer
For each customer, return their top 2 highest-value orders.
👉 Use ROW_NUMBER() with partition.
With ct1 as
Select customer_id,order_id,row_number()over(partition by customer_id order by total_amount
desc) as rn
From orders
)
Select * from ct1 where rn <=2
🔟 Revenue Difference Between Current and Previous Order
For each customer, calculate:
difference in amount between current order and previous order.
👉 Use LAG().
With ct1 as
(Select customer_id,order_id,total_amount as current_order,lag(total_amount)over(partition by
customer_id, order by order_id) as previous_order
From orders)
Select customer_id,order_id,(current_order – previous_order) as diff
From ct 1;
🎯 Rules
Use clean SQL
Prefer CTEs when needed
Assume PostgreSQL / Snowflake syntax
Think like dashboard logic
🚀 Difficulty Level
Day 1 → Intermediate
Day 2 → Strong Intermediate
Day 3 → Advanced Logic
Send Question 1 when ready.
Let’s build real analytical muscle 💪
Memory ful