SELECT
  machine_id,
  SUM(total_revenue) AS total_revenue,
  SUM(net_margin)    AS total_net_margin,
  SUM(sold_qty)      AS total_units
FROM dataset_itemsale
WHERE project_id = 1
GROUP BY machine_id
ORDER BY total_revenue DESC;

SELECT
  location_name,
  SUM(total_revenue) AS total_revenue,
  SUM(net_margin)    AS total_net_margin
FROM dataset_itemsale
WHERE project_id = 1
GROUP BY location_name
ORDER BY total_revenue DESC;

SELECT
  item_id,
  item_name,
  SUM(total_revenue) AS total_revenue,
  SUM(sold_qty)      AS qty_sold
FROM dataset_itemsale
WHERE project_id = 1
GROUP BY item_id, item_name
ORDER BY total_revenue DESC
LIMIT 20;

SELECT
  route_name,
  driver,
  COUNT(DISTINCT visit_id) AS visits,
  SUM(total_revenue)       AS total_revenue,
  SUM(net_margin)          AS total_net_margin
FROM dataset_itemsale
WHERE project_id = 1
GROUP BY route_name, driver
ORDER BY total_revenue DESC;

SELECT
  machine_id,
  COUNT(*) FILTER (WHERE has_price_mismatch)           AS price_mismatch,
  COUNT(*) FILTER (WHERE has_pending_planogram_change) AS planogram_issues
FROM dataset_itemsale
WHERE project_id = 1
GROUP BY machine_id
HAVING
  COUNT(*) FILTER (WHERE has_price_mismatch) > 0
  OR COUNT(*) FILTER (WHERE has_pending_planogram_change) > 0;

SELECT
  reason,
  COUNT(*) AS rows_count
FROM upload_quarantine
GROUP BY reason
ORDER BY rows_count DESC;