pinot_sql_example.sql

-- products
SELECT
  COUNT(product_id) AS product_count,
  AVG(price) AS avg_price,
  AVG(cogs) AS avg_cogs,
  AVG(price) - AVG(cogs) AS avg_gross_profit
FROM
  products;

-- purchases
SELECT
  product_id,
  SUMPRECISION(quantity, 10, 0) AS quantity,
  SUMPRECISION(total_purchase, 10, 2) AS sales
FROM
  purchases
GROUP BY
  product_id
ORDER BY
  sales DESC;

-- purchasesEnriched
SELECT
  product_id,
  product_name,
  product_category,
  SUMPRECISION(purchase_quantity, 10, 0) AS quantity,
  SUMPRECISION(total_purchase, 10, 2) AS sales
FROM
  purchasesEnriched
GROUP BY
  product_id,
  product_name,
  product_category
ORDER BY
  sales DESC;