SQL Business Insights Analysis

SELECT
    SUM(od.quantity * p.selling_price) AS total_revenue
FROM
    order_details od
    JOIN products p ON od.product_id = p.product_id;
        

SELECT
    p.product_name,
    SUM(od.quantity * p.selling_price) AS revenue
FROM
    order_details od
    JOIN products p ON od.product_id = p.product_id
GROUP BY
    p.product_name
ORDER BY
    revenue DESC
LIMIT
    5;
        

SELECT
    DATE_TRUNC ('month', o.order_date) AS month,
    SUM(od.quantity * p.selling_price) AS revenue
FROM
    orders o
    JOIN order_details od ON o.order_id = od.order_id
    JOIN products p ON od.product_id = p.product_id
GROUP BY
    month
ORDER BY
    month;
        

SELECT
    c.customer_id,
    c.customer_name,
    SUM(od.quantity * p.selling_price) AS customer_lifetime_value
FROM
    customers c
    JOIN orders o ON c.customer_id = o.customer_id
    JOIN order_details od ON o.order_id = od.order_id
    JOIN products p ON od.product_id = p.product_id
GROUP BY
    c.customer_id,
    c.customer_name
ORDER BY
    customer_lifetime_value DESC;
        

WITH
  customer_revenue AS (
    SELECT
      c.customer_id,
      SUM(od.quantity * p.selling_price) AS revenue
    FROM
      customers c
      JOIN orders o ON c.customer_id = o.customer_id
      JOIN order_details od ON o.order_id = od.order_id
      JOIN products p ON od.product_id = p.product_id
    GROUP BY
      c.customer_id
  ),
  ranked_customers AS (
    SELECT
      customer_id,
      revenue,
      NTILE (10) OVER (
        ORDER BY
          revenue DESC
      ) AS decile
    FROM
      customer_revenue
  )
SELECT
  SUM(revenue) AS top_10_percent_revenue
FROM
  ranked_customers
WHERE
  decile = 1;