1. Find customers who placed more than 3 orders in the last 6 months.

SELECT customer_id
FROM orders
WHERE order_date >= DATEADD(MONTH, -6, GETDATE())
GROUP BY customer_id
HAVING COUNT(*) > 3;

2. List customers with their latest and second latest order dates.

WITH ranked_orders AS (
SELECT customer_id, order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders
)
SELECT customer_id, order_date
FROM ranked_orders
WHERE rn IN (1, 2);

3. Identify customers who have never placed any order.

SELECT c.customer_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

4. For each customer, calculate the average number of days between their orders.

WITH order_dates AS (
SELECT customer_id, order_date,
LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_date
FROM orders
)
SELECT customer_id,
AVG(DATEDIFF(DAY, prev_order_date, order_date)) AS avg_days_between_orders
FROM order_dates
WHERE prev_order_date IS NOT NULL
GROUP BY customer_id;

5. Find the top 5 customers by total number of orders.

SELECT TOP 5 customer_id, COUNT(*) AS total_orders

FROM orders
GROUP BY customer_id
ORDER BY total_orders DESC;

6. Show month-wise order count for the past year.

SELECT YEAR(order_date) AS year, MONTH(order_date) AS month, COUNT(*) AS order_count

FROM orders
WHERE order_date >= DATEADD(YEAR, -1, GETDATE())
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY year, month;

7. Find customers whose first order was made more than a year after their registration date.

WITH first_orders AS (
 SELECT customer_id, MIN(order_date) AS first_order_date
FROM orders
GROUP BY customer_id
)
SELECT c.customer_id
FROM customers c
JOIN first_orders f ON c.customer_id = f.customer_id
WHERE DATEDIFF(DAY, c.registration_date, f.first_order_date) > 365;

8. Find the customer who placed the most recent order.

SELECT TOP 1 customer_id, order_date

FROM orders
ORDER BY order_date DESC;

9. List customers who placed orders in every month of the current year.

WITH months AS (
SELECT DISTINCT MONTH(order_date) AS month
FROM orders
WHERE YEAR(order_date) = YEAR(GETDATE())
),
customer_months AS (
SELECT customer_id, MONTH(order_date) AS month
FROM orders
WHERE YEAR(order_date) = YEAR(GETDATE())
GROUP BY customer_id, MONTH(order_date)
)
SELECT customer_id
FROM customer_months
GROUP BY customer_id
HAVING COUNT(DISTINCT month) = (SELECT COUNT(*) FROM months);

10. For each customer, find the order which had the longest gap from their previous order.

WITH order_gaps AS (
SELECT customer_id, order_id, order_date,
LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_date
FROM orders
),
gaps_with_diff AS (
SELECT customer_id, order_id, order_date,
DATEDIFF(DAY, prev_order_date, order_date) AS gap_days
FROM order_gaps
WHERE prev_order_date IS NOT NULL
),
max_gap_per_customer AS (
SELECT customer_id, MAX(gap_days) AS max_gap
FROM gaps_with_diff
GROUP BY customer_id
)
SELECT g.customer_id, g.order_id, g.order_date, g.gap_days
FROM gaps_with_diff g
JOIN max_gap_per_customer m
ON g.customer_id = m.customer_id AND g.gap_days = m.max_gap;
11. Find customers who placed exactly one order in total.

SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(*) = 1;

12. Get the total number of orders placed on weekends (Saturday or Sunday).

SELECT COUNT(*) AS weekend_orders

FROM orders
WHERE DATENAME(WEEKDAY, order_date) IN ('Saturday', 'Sunday');

13. List customers who placed an order in January and also in March of the same year.

SELECT customer_id, YEAR(order_date) AS order_year

FROM orders
GROUP BY customer_id, YEAR(order_date)
HAVING SUM(CASE WHEN MONTH(order_date) = 1 THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN MONTH(order_date) = 3 THEN 1 ELSE 0 END) > 0;

14. For each customer, find their first and last order dates.

SELECT customer_id,
MIN(order_date) AS first_order_date,
MAX(order_date) AS last_order_date
FROM orders
GROUP BY customer_id;

15. Find customers whose orders are spread across more than 2 different years.

SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(DISTINCT YEAR(order_date)) > 2;

16. List all customers who placed two consecutive orders within 1 day.

WITH order_diff AS (
SELECT customer_id, order_date,
LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_date
FROM orders
)
SELECT DISTINCT customer_id
FROM order_diff
WHERE DATEDIFF(DAY, prev_order_date, order_date) = 1;

17. Find customers whose most recent order is older than 1 year from today.

WITH latest_order AS (
SELECT customer_id, MAX(order_date) AS last_order_date
FROM orders
GROUP BY customer_id
)
SELECT customer_id
FROM latest_order
WHERE last_order_date < DATEADD(YEAR, -1, GETDATE());
18. Get the cumulative count of orders for each customer by date (running total).

SELECT customer_id, order_date,

COUNT(*) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED
PRECEDING AND CURRENT ROW) AS running_total
FROM orders;

19. List customers who placed more than 5 orders but have not ordered anything in the last 3 months.

WITH total_and_latest AS (
SELECT customer_id,
COUNT(*) AS total_orders,
MAX(order_date) AS last_order_date
FROM orders
GROUP BY customer_id
)
SELECT customer_id
FROM total_and_latest
WHERE total_orders > 5 AND last_order_date < DATEADD(MONTH, -3, GETDATE());

20. Show a ranking of customers based on total number of orders, with ties given the same rank.

SELECT customer_id, COUNT(*) AS order_count,

RANK() OVER (ORDER BY COUNT(*) DESC) AS customer_rank
FROM orders
GROUP BY customer_id;

21. Find customers who havent placed any order in the last 6 months.

SELECT customer_id
FROM customers
WHERE customer_id NOT IN (
SELECT DISTINCT customer_id
FROM orders
WHERE order_date >= DATEADD(MONTH, -6, GETDATE())
);