SQL to ERD

From SQL JOIN statements to ER diagram in a seconds 🎉

From SQL to ER digram

-- Monthly Top Customer Sales

WITH monthly_sales AS (
    SELECT
      DATE_TRUNC('month', o.order_date) AS month,
      c.customer_id,
      c.customer_name,
      p.product_name,
      SUM(oi.quantity * oi.unit_price) AS revenue,
      ROW_NUMBER() OVER (PARTITION BY DATE_TRUNC('month', o.order_date), c.customer_id ORDER BY SUM(oi.quantity * oi.unit_price) DESC) AS revenue_rank
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    WHERE
      o.order_date >= '2024-01-01'
      AND o.order_date <= '2024-12-31'
    GROUP BY
      DATE_TRUNC('month', o.order_date),
      c.customer_id,
      c.customer_name,
      p.product_name
  ),
top_sales AS (
    SELECT
      ms.month,
      ms.customer_id,
      ms.customer_name,
      ms.product_name,
      ms.revenue,
      ms.revenue_rank
    FROM
      monthly_sales ms
    WHERE
      revenue_rank <= 3
  ),
customer_total_revenue AS (
    SELECT
      c.customer_id,
      SUM(oi.quantity * oi.unit_price) AS total_revenue
    FROM
      orders o
    JOIN customers c ON o.customer_id = c.customer_id
    JOIN order_items oi ON o.order_id = oi.order_id
    WHERE
      o.order_date >= '2024-01-01'
      AND o.order_date <= '2024-12-31'
    GROUP BY
      c.customer_id
  ),
final as (
  SELECT
    tps.month,
    tps.customer_id,
    tps.customer_name,
    tps.product_name,
    tps.revenue,
    CASE
      WHEN revenue_rank = 1 THEN 'High'
      WHEN revenue_rank = 2 THEN 'Medium'
      ELSE 'Low'
    END AS revenue_category
  FROM
    top_sales tps
  JOIN
    customer_total_revenue ctr
  ON
    tps.customer_id = ctr.customer_id
  WHERE
    ctr.total_revenue >= 10000
  ORDER BY
    month ASC,
    revenue_rank ASC
)

SELECT * FROM final;