Request:We need a comprehensive report of all 2024 orders showing customer details, what they ordered, any loyalty promotions they used, which cafe and barista served them, and flag customers who haven't been active recently so we can target them with re-engagement campaigns.
The Query
Normal
0 / 10 fixed
1
-- This query gets all the order details we need for the annual review
2
-- Works fine on my dev database!
3
SELECT DISTINCT
4
    *,
5
    (SELECT MAX(oi2.unit_price)
6
     FROM order_items oi2
7
     WHERE oi2.order_id = o.order_id) AS max_item_price,
8
    (SELECT COUNT(*)
9
     FROM order_items oi3
10
     WHERE oi3.order_id = o.order_id) AS item_count
11
FROM orders o
12
INNER JOIN customers c ON c.customer_id = o.customer_id
13
INNER JOIN customer_addresses ca ON ca.customer_id = c.customer_id
14
    AND UPPER(ca.address_type) = 'BILLING'
15
INNER JOIN employees e ON e.employee_id = o.employee_id
16
INNER JOIN cafes cf ON cf.cafe_id = o.cafe_id
17
LEFT JOIN order_items oi ON oi.order_id = o.order_id
18
LEFT JOIN products p ON p.product_id = oi.product_id
19
LEFT JOIN order_discounts od ON od.order_id = o.order_id
20
LEFT JOIN promotions promo ON promo.promotion_id = od.promotion_id
21
WHERE YEAR(o.order_date) = 2024
22
    AND o.status = 'completed'
23
    AND c.loyalty_tier = '3'
24
    AND (c.email LIKE '%@gmail.com' OR c.email LIKE '%@yahoo.com')
25
    AND c.customer_id NOT IN (
26
        SELECT customer_id
27
        FROM customers
28
        WHERE status = 'inactive'
29
    )
30
    AND (
31
        SELECT COUNT(*)
32
        FROM orders o2
33
        WHERE o2.customer_id = c.customer_id
34
            AND o2.order_date > CURRENT_DATE - INTERVAL '90 days'
35
            AND o2.order_id != o.order_id
36
    ) = 0
37
    AND cf.region IN (
38
        SELECT DISTINCT s.region
39
        FROM suppliers s
40
        WHERE LOWER(s.supplier_name) LIKE '%seasonal%'
41
            OR LOWER(s.supplier_name) LIKE '%organic%'
42
    )
43
ORDER BY o.order_date DESC;