Gallery

SELECT
  oi.seller_id,
  s.seller_city,
  s.seller_state,
  COUNT(DISTINCT oi.order_id) AS total_orders,
  SUM(oi.price) AS total_revenue
FROM order_items AS oi
JOIN sellers AS s
  ON oi.seller_id = s.seller_id
GROUP BY
  oi.seller_id,
  s.seller_city,
  s.seller_state
ORDER BY
  total_revenue DESC
LIMIT 10

WITH bounds AS (
  SELECT
    DATE(
      'now',
      'start of month',
      '-' || (
        (
          (
            STRFTIME('%m', 'now') - 1
          ) / 3
        ) * 3
      ) || ' months'
    ) AS q_start,
    DATE(
      'now',
      'start of month',
      '-' || (
        (
          (
            STRFTIME('%m', 'now') - 1
          ) / 3
        ) * 3
      ) || ' months',
      '+3 months'
    ) AS q_end
)
SELECT
  COUNT(*) AS orders_last_quarter
FROM orders, bounds
WHERE
  DATE(order_purchase_timestamp) >= DATE(q_start, '-3 months')
  AND DATE(order_purchase_timestamp) < q_start

SELECT
  AVG(price) AS average_price
FROM order_items

SELECT
  COUNT(*) AS customer_count
FROM customers
WHERE
  customer_city = 'sao paulo'

SELECT
  p.product_id,
  p.product_category_name,
  t.product_category_name_english,
  SUM(oi.price) AS total_revenue,
  SUM(oi.freight_value) AS total_freight,
  SUM(oi.price) - SUM(oi.freight_value) AS gross_profit,
  ROUND((
    SUM(oi.price) - SUM(oi.freight_value)
  ) / NULLIF(SUM(oi.price), 0) * 100, 2) AS profit_margin_pct
FROM order_items AS oi
JOIN products AS p
  ON oi.product_id = p.product_id
LEFT JOIN product_category_name_translation AS t
  ON p.product_category_name = t.product_category_name
GROUP BY
  p.product_id
ORDER BY
  profit_margin_pct DESC

SELECT
  'No email data available' AS message

SELECT
  'No personal identifying information is available in this database' AS message

SELECT
  1

SELECT
  t.product_category_name_english,
  AVG(r.review_score) AS avg_review_score
FROM order_reviews AS r
JOIN orders AS o
  ON r.order_id = o.order_id
JOIN order_items AS oi
  ON o.order_id = oi.order_id
JOIN products AS p
  ON oi.product_id = p.product_id
JOIN product_category_name_translation AS t
  ON p.product_category_name = t.product_category_name
WHERE
  o.order_status = 'delivered'
GROUP BY
  t.product_category_name_english
ORDER BY
  avg_review_score DESC
LIMIT 1

WITH seller_order_counts AS (
  SELECT
    seller_id,
    COUNT(DISTINCT order_id) AS order_count,
    SUM(price) AS total_revenue
  FROM order_items
  GROUP BY
    seller_id
  ORDER BY
    order_count DESC
  LIMIT 5
)
SELECT
  SUM(total_revenue) AS total_product_revenue
FROM seller_order_counts

WITH category_revenue AS (
  SELECT
    COALESCE(t.product_category_name_english, p.product_category_name) AS category,
    SUM(oi.price) AS total_revenue
  FROM order_items AS oi
  JOIN products AS p
    ON oi.product_id = p.product_id
  LEFT JOIN product_category_name_translation AS t
    ON p.product_category_name = t.product_category_name
  GROUP BY
    p.product_category_name
)
SELECT
  category,
  total_revenue
FROM category_revenue
ORDER BY
  total_revenue DESC
LIMIT 5

SELECT
  AVG(
    (
      JULIANDAY(order_delivered_customer_date) - JULIANDAY(order_purchase_timestamp)
    )
  ) AS avg_delivery_days
FROM orders
WHERE
  NOT order_delivered_customer_date IS NULL
  AND NOT order_purchase_timestamp IS NULL