OFN global key stats

ofn-global-stats.sh

sql='
SELECT
    SUBSTRING(CONCAT(completed_at) from 1 for 7) AS month,
    COUNT(DISTINCT distributor_id) AS shops,
    COUNT(DISTINCT email) AS customers,
    COUNT(o.id) AS orders,
    SUM(o.total) AS VALUE
  FROM spree_orders o
  WHERE o.completed_at >= DATE_TRUNC($$year$$, now() - interval $$2 months$$)
    AND o.state = $$complete$$ GROUP BY month ORDER BY month;
'
ansible all-prod -u openfoodnetwork -a "psql -h localhost openfoodnetwork ofn_user -c '$sql'"

sql='
SELECT
    SUBSTRING(CONCAT(completed_at) from 1 for 7) AS month,
    COUNT(DISTINCT distributor_id) AS shops,
    COUNT(DISTINCT order_suppliers.supplier_id) AS suppliers,
    COUNT(DISTINCT order_enterprises.enterprise_id) AS enterprises,
    COUNT(DISTINCT email) AS customers,
    COUNT(DISTINCT o.id) AS orders
  FROM spree_orders o
    LEFT JOIN (
      SELECT order_id, supplier_id
        FROM spree_line_items
          LEFT JOIN spree_variants ON spree_variants.id = spree_line_items.variant_id
          LEFT JOIN spree_products ON spree_products.id = spree_variants.product_id
    ) order_suppliers ON o.id = order_suppliers.order_id
    LEFT JOIN (
      SELECT order_id, supplier_id AS enterprise_id
        FROM spree_line_items
          LEFT JOIN spree_variants ON spree_variants.id = spree_line_items.variant_id
          LEFT JOIN spree_products ON spree_products.id = spree_variants.product_id
      UNION
      SELECT id as order_id, distributor_id AS enterprise_id
        FROM spree_orders
    ) order_enterprises ON o.id = order_enterprises.order_id
  WHERE o.completed_at >= DATE_TRUNC($$year$$, now() - interval $$2 months$$)
    AND o.state = $$complete$$ GROUP BY month ORDER BY month;
'
ansible all-prod -u openfoodnetwork -a "psql -h localhost openfoodnetwork ofn_user -c '$sql'"