MySQL Interview Queries 1 — Practical SQL

SELECT c.id, c.name, c.email, MAX(o.created_at) as last_order
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id
HAVING last_order IS NULL OR last_order < DATE_SUB(CURDATE(), INTERVAL 6 MONTH);

SELECT p.category, COUNT(oi.id) as items_sold, AVG(oi.quantity * oi.price) as avg_order_value
FROM order_items oi
JOIN products p ON oi.product_id = p.id
GROUP BY p.category
ORDER BY avg_order_value DESC;

SELECT email, COUNT(*) as count
FROM customers
GROUP BY email
HAVING count > 1;

SELECT MONTH(order_date) as month, SUM(amount) as monthly_sales, COUNT(*) as order_count
FROM orders
WHERE YEAR(order_date) = YEAR(CURDATE())
GROUP BY MONTH(order_date)
ORDER BY month;

SELECT c.id, c.name, o.id as order_id, o.amount
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.amount > (SELECT AVG(amount) FROM orders)
ORDER BY o.amount DESC;

SELECT p.id, p.name, SUM(oi.quantity * oi.price) as revenue, SUM(oi.quantity) as units_sold
FROM products p
JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id
ORDER BY revenue DESC
LIMIT 1;

SELECT o.id, o.customer_id, COUNT(oi.id) as item_count, SUM(oi.quantity) as total_quantity
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.id
ORDER BY item_count DESC
LIMIT 10;

SELECT 
  customer_id,
  COUNT(*) as order_count,
  CASE 
    WHEN COUNT(*) = 1 THEN 'First-time'
    WHEN COUNT(*) BETWEEN 2 AND 5 THEN 'Repeat'
    ELSE 'Loyal'
  END as segment
FROM orders
GROUP BY customer_id;

SELECT p.id, p.name, MAX(o.order_date) as last_sold
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.id
GROUP BY p.id
HAVING last_sold IS NULL OR last_sold < DATE_SUB(CURDATE(), INTERVAL 30 DAY);

SELECT 
  DATE(order_date) as order_date,
  SUM(amount) as daily_sales,
  SUM(SUM(amount)) OVER (ORDER BY DATE(order_date)) as running_total
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY DATE(order_date);

SELECT 
  c.country,
  c.name,
  SUM(o.amount) as total_spent,
  RANK() OVER (PARTITION BY c.country ORDER BY SUM(o.amount) DESC) as rank_in_country
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id
ORDER BY c.country, rank_in_country;

SELECT 
  YEAR(order_date) as year,
  MONTH(order_date) as month,
  SUM(amount) as monthly_sales,
  LAG(SUM(amount)) OVER (ORDER BY YEAR(order_date), MONTH(order_date)) as previous_month_sales
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date);

SELECT c.id, c.name, COUNT(DISTINCT p.category) as categories_purchased
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
GROUP BY c.id
HAVING categories_purchased = (SELECT COUNT(DISTINCT category) FROM products);

WITH top_customers AS (
  SELECT c.id
  FROM customers c
  JOIN orders o ON c.id = o.customer_id
  GROUP BY c.id
  ORDER BY SUM(o.amount) DESC
  LIMIT 10
)
SELECT p.name, COUNT(oi.id) as order_count
FROM products p
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
WHERE o.customer_id IN (SELECT id FROM top_customers)
GROUP BY p.id
ORDER BY order_count DESC;

SELECT o.id, o.created_at, GROUP_CONCAT(DISTINCT p.category) as categories
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
GROUP BY o.id
HAVING COUNT(DISTINCT p.category) = 1;

SELECT 
  AVG(amount) as average,
  STDDEV(amount) as std_dev,
  AVG(CASE 
    WHEN ABS(amount - (SELECT AVG(amount) FROM orders)) <= 2 * STDDEV(amount) THEN amount
  END) as average_cleaned
FROM orders;

WITH monthly_sales AS (
  SELECT 
    p.id, p.name,
    YEAR(o.order_date) as year,
    MONTH(o.order_date) as month,
    SUM(oi.quantity) as units
  FROM products p
  JOIN order_items oi ON p.id = oi.product_id
  JOIN orders o ON oi.order_id = o.id
  WHERE o.order_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
  GROUP BY p.id, year, month
)
SELECT * FROM monthly_sales m
ORDER BY m.id, m.year, m.month;

SELECT 
  c.id, c.name,
  o1.id as order1_id, o1.created_at as order1_date,
  o2.id as order2_id, o2.created_at as order2_date,
  DATEDIFF(o2.created_at, o1.created_at) as days_between
FROM customers c
JOIN orders o1 ON c.id = o1.customer_id
JOIN orders o2 ON c.id = o2.customer_id
WHERE o1.id < o2.id AND DATEDIFF(o2.created_at, o1.created_at) <= 30
ORDER BY c.id, days_between;

WITH customer_cohorts AS (
  SELECT 
    c.id,
    DATE_FORMAT(MIN(o.created_at), '%Y-%m') as cohort_month,
    DATE_FORMAT(o.created_at, '%Y-%m') as order_month
  FROM customers c
  JOIN orders o ON c.id = o.customer_id
  GROUP BY c.id, order_month
)
SELECT cohort_month, order_month, COUNT(DISTINCT id) as customer_count
FROM customer_cohorts
GROUP BY cohort_month, order_month
ORDER BY cohort_month, order_month;