SELECT
  o.order_id,
  c.first_name || ' ' || c.last_name AS customer_name,
  o.order_date,
  o.order_status,
  o.net_total
FROM sales.orders o
INNER JOIN customers.customers c
  ON o.cust_id = c.customer_id
ORDER BY o.order_date DESC
LIMIT 10;

SELECT
  e.first_name || ' ' || e.last_name AS employee,
  e.role,
  d.dept_name AS department,
  s.store_name,
  e.salary
FROM stores.employees e
INNER JOIN core.dim_department d
  ON e.dept_id = d.dept_id
INNER JOIN stores.stores s
  ON e.store_id = s.store_id
ORDER BY d.dept_name, e.salary DESC
LIMIT 20;

SELECT
  o.order_id,
  o.order_date,
  c.first_name || ' ' || c.last_name AS customer,
  p.product_name,
  oi.quantity,
  oi.unit_price,
  oi.net_amount
FROM sales.orders o
INNER JOIN customers.customers c
  ON o.cust_id = c.customer_id
INNER JOIN sales.order_items oi
  ON o.order_id = oi.order_id
INNER JOIN products.products p
  ON oi.prod_id = p.product_id
WHERE o.order_status = 'Delivered'
ORDER BY o.order_date DESC
LIMIT 15;

SELECT
  cat.category_name,
  COUNT(*) AS items_sold,
  COUNT(DISTINCT p.product_id) AS distinct_products,
  ROUND(SUM(oi.net_amount), 0) AS total_revenue,
  ROUND(AVG(oi.unit_price), 0) AS avg_unit_price
FROM sales.order_items oi
JOIN products.products p
  ON oi.prod_id = p.product_id
JOIN core.dim_brand b
  ON p.brand_id = b.brand_id
JOIN core.dim_category cat
  ON b.category_id = cat.category_id
GROUP BY cat.category_name
HAVING COUNT(*) >= 1000
ORDER BY total_revenue DESC
LIMIT 20;

SELECT
  c.customer_id,
  c.first_name || ' ' || c.last_name AS customer,
  COUNT(o.order_id) AS order_count
FROM customers.customers c
LEFT JOIN sales.orders o
  ON c.customer_id = o.cust_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY order_count ASC
LIMIT 15;

SELECT
  c.customer_id,
  c.first_name || ' ' || c.last_name AS customer,
  c.email,
  c.registration_date,
  EXTRACT(YEAR FROM AGE(c.registration_date)) AS years_since_reg
FROM customers.customers c
LEFT JOIN sales.orders o
  ON c.customer_id = o.cust_id
WHERE o.order_id IS NULL
ORDER BY c.registration_date
LIMIT 20;

SELECT
  p.product_id,
  p.product_name,
  p.price AS listed_price,
  COALESCE(SUM(oi.quantity), 0) AS total_qty_sold,
  COALESCE(ROUND(SUM(oi.net_amount), 0), 0) AS total_revenue,
  CASE
    WHEN SUM(oi.quantity) IS NULL THEN 'Never Sold'
    WHEN SUM(oi.quantity) < 10 THEN 'Low Seller'
    WHEN SUM(oi.quantity) < 100 THEN 'Popular'
    ELSE 'Best Seller'
  END AS sales_tier
FROM products.products p
LEFT JOIN sales.order_items oi
  ON p.product_id = oi.prod_id
GROUP BY p.product_id, p.product_name, p.price
ORDER BY total_qty_sold DESC
LIMIT 20;

SELECT
  c.customer_id,
  c.first_name || ' ' || c.last_name AS customer,
  COALESCE(t.tier_name, 'Not a Member') AS tier,
  COALESCE(m.points_balance, 0) AS points_balance,
  COALESCE(red.total_redeemed, 0) AS lifetime_redeemed,
  COALESCE(red.redemption_count, 0) AS times_redeemed
FROM customers.customers c
LEFT JOIN loyalty.members m
  ON c.customer_id = m.customer_id
LEFT JOIN loyalty.tiers t
  ON m.tier_id = t.tier_id
LEFT JOIN (
  SELECT customer_id,
    SUM(points_redeemed) AS total_redeemed,
    COUNT(*) AS redemption_count
  FROM loyalty.redemptions
  GROUP BY customer_id
) red ON c.customer_id = red.customer_id
ORDER BY m.points_balance DESC NULLS LAST
LIMIT 20;

SELECT
  d.dept_id,
  d.dept_name,
  COUNT(e.employee_id) AS staff_count,
  COALESCE(ROUND(AVG(e.salary), 0), 0) AS avg_salary
FROM stores.employees e
RIGHT JOIN core.dim_department d
  ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY staff_count DESC;

SELECT
  s.store_id,
  s.store_name,
  s.city,
  COUNT(o.order_id) AS total_orders,
  COALESCE(ROUND(SUM(o.net_total), 0), 0) AS gross_sales,
  COALESCE(ROUND(AVG(o.net_total), 0), 0) AS avg_ticket_size
FROM stores.stores s
LEFT JOIN sales.orders o
  ON s.store_id = o.store_id
GROUP BY s.store_id, s.store_name, s.city
ORDER BY gross_sales DESC;

SELECT
  o.order_id,
  c.first_name || ' ' || c.last_name AS customer,
  s.store_name,
  p.product_name,
  oi.quantity,
  oi.net_amount,
  COALESCE(pay.payment_mode, 'Pending') AS payment_mode
FROM sales.orders o
INNER JOIN customers.customers c ON o.cust_id = c.customer_id
INNER JOIN stores.stores s ON o.store_id = s.store_id
INNER JOIN sales.order_items oi ON o.order_id = oi.order_id
INNER JOIN products.products p ON oi.prod_id = p.product_id
LEFT JOIN sales.payments pay ON o.order_id = pay.order_id
WHERE o.order_status = 'Delivered'
ORDER BY o.order_date DESC
LIMIT 10;

SELECT
  e.employee_id,
  e.first_name || ' ' || e.last_name AS staff_name,
  e.role,
  COUNT(c.call_id) AS calls_handled,
  COALESCE(ROUND(AVG(c.call_duration_seconds)), 0) AS avg_duration_sec
FROM stores.employees e
LEFT JOIN call_center.calls c
  ON e.employee_id = c.agent_id
WHERE e.role ILIKE '%Support%' OR e.role ILIKE '%Service%' OR e.role ILIKE '%Associate%'
GROUP BY e.employee_id, e.first_name, e.last_name, e.role
ORDER BY calls_handled DESC
LIMIT 15;