Common SQL Queries (on-going)

Last Month Metrics or Data

You can get data for any number of past months by changing -1 month in the SQL query {"(INTERVAL '-1 month')"} to the number of months you need it for. Data will be grouped by month.

Getting last month sales for an instance

SELECT date_trunc('month', "public"."spree_orders"."completed_at") AS "completed_at", sum("public"."spree_orders"."total") AS "value"|FROM "public"."spree_orders"WHERE ("public"."spree_orders"."state" = 'complete'   AND "public"."spree_orders"."completed_at" >= date_trunc('month', CAST((CAST(now() AS timestamp) + (INTERVAL '-1 month')) AS timestamp)) AND "public"."spree_orders"."completed_at" < date_trunc('month', CAST(now() AS timestamp)))GROUP BY date_trunc('month', "public"."spree_orders"."completed_at")ORDER BY date_trunc('month', "public"."spree_orders"."completed_at") ASC

Getting last month's completed orders for an instance

SELECT date_trunc('month', "public"."spree_orders"."completed_at") AS "completed_at", count(*) AS "value" FROM "public"."spree_orders" WHERE ("public"."spree_orders"."state" = 'complete'    AND "public"."spree_orders"."completed_at" >= date_trunc('month', CAST((CAST(now() AS timestamp) + (INTERVAL '-1 month')) AS timestamp)) AND "public"."spree_orders"."completed_at" < date_trunc('month', CAST(now() AS timestamp))) GROUP BY date_trunc('month', "public"."spree_orders"."completed_at") ORDER BY date_trunc('month', "public"."spree_orders"."completed_at") ASC

Getting last month's new enterprise signups for an instance

SELECT date_trunc('month', "public"."enterprises"."created_at") AS "created_at", count(distinct "public"."enterprises"."id") AS "value"|FROM "public"."enterprises"WHERE ("public"."enterprises"."created_at" >= date_trunc('month', CAST((CAST(now() AS timestamp) + (INTERVAL '-1 month')) AS timestamp))   AND "public"."enterprises"."created_at" < date_trunc('month', CAST(now() AS timestamp)))GROUP BY date_trunc('month', "public"."enterprises"."created_at")ORDER BY date_trunc('month', "public"."enterprises"."created_at") ASC

Getting number of shoppers with a completed order in last month for an instance

SELECT date_trunc('month', "public"."spree_orders"."completed_at") AS "completed_at", count(distinct "public"."spree_orders"."email") AS "value"|FROM "public"."spree_orders"WHERE ("public"."spree_orders"."state" = 'complete'   AND "public"."spree_orders"."completed_at" >= date_trunc('month', CAST((CAST(now() AS timestamp) + (INTERVAL '-1 month')) AS timestamp)) AND "public"."spree_orders"."completed_at" < date_trunc('month', CAST(now() AS timestamp)))GROUP BY date_trunc('month', "public"."spree_orders"."completed_at")ORDER BY date_trunc('month', "public"."spree_orders"."completed_at") ASC

Getting number of Active Shopfronts in last month for an instance

Number of shopfronts/shops with at least 1 completed order (can incl. test orders) from their shop

SELECT date_trunc('month', "Spree Orders"."completed_at") AS "completed_at", count(distinct "public"."enterprises"."id") AS "value"|FROM "public"."enterprises"LEFT JOIN "public"."spree_orders" "Spree Orders" ON "public"."enterprises"."id" = "Spree Orders"."distributor_id"WHERE ("Spree Orders"."state" = 'complete'   AND "Spree Orders"."completed_at" >= date_trunc('month', CAST((CAST(now() AS timestamp) + (INTERVAL '-1 month')) AS timestamp)) AND "Spree Orders"."completed_at" < date_trunc('month', CAST(now() AS timestamp)))GROUP BY date_trunc('month', "Spree Orders"."completed_at")ORDER BY date_trunc('month', "Spree Orders"."completed_at") ASC

Getting number of Active Producers in last month for an instance

Number of producers who has an item sold in the last month via any shop

SELECT date_trunc('month', "public"."spree_orders"."completed_at") AS "completed_at", count(distinct "Enterprises"."id") AS "value"|FROM "public"."spree_orders"LEFT JOIN "public"."spree_line_items" "Spree Line Items" ON "public"."spree_orders"."id" = "Spree Line Items"."order_id" LEFT JOIN "public"."spree_variants" "Spree Variants" ON "Spree Line Items"."variant_id" = "Spree Variants"."id" LEFT JOIN "public"."spree_products" "Spree Products" ON "Spree Variants"."product_id" = "Spree Products"."id" LEFT JOIN "public"."enterprises" "Enterprises" ON "Spree Products"."supplier_id" = "Enterprises"."id"WHERE ("public"."spree_orders"."completed_at" >= date_trunc('month', CAST((CAST(now() AS timestamp) + (INTERVAL '-1 month')) AS timestamp))   AND "public"."spree_orders"."completed_at" < date_trunc('month', CAST(now() AS timestamp)) AND "public"."spree_orders"."state" = 'complete')GROUP BY date_trunc('month', "public"."spree_orders"."completed_at")ORDER BY date_trunc('month', "public"."spree_orders"."completed_at") ASC

Getting last month's sale by individual shopfronts for an instance

Shop filter: Sells = own

SELECT date_trunc('month', "public"."spree_orders"."completed_at") AS "completed_at", sum("public"."spree_orders"."total") AS "value"
FROM "public"."spree_orders"
LEFT JOIN "public"."enterprises" "enterprises__via__distributor_" ON "public"."spree_orders"."distributor_id" = "enterprises__via__distributor_"."id"
WHERE ("public"."spree_orders"."state" = 'complete'
   AND "public"."spree_orders"."completed_at" >= date_trunc('month', CAST((CAST(now() AS timestamp) + (INTERVAL '-1 month')) AS timestamp)) AND "public"."spree_orders"."completed_at" < date_trunc('month', CAST(now() AS timestamp)) AND "enterprises__via__distributor_"."sells" = 'own')
GROUP BY date_trunc('month', "public"."spree_orders"."completed_at")
ORDER BY date_trunc('month', "public"."spree_orders"."completed_at") ASC

Getting last month's sale by hub shopfronts for an instance

Shop filter: Sells = any

SELECT date_trunc('month', "public"."spree_orders"."completed_at") AS "completed_at", sum("public"."spree_orders"."total") AS "value"|FROM "public"."spree_orders"LEFT JOIN "public"."enterprises" "enterprises__via__distributor_" ON "public"."spree_orders"."distributor_id" = "enterprises__via__distributor_"."id"WHERE ("public"."spree_orders"."state" = 'complete'   AND "public"."spree_orders"."completed_at" >= date_trunc('month', CAST((CAST(now() AS timestamp) + (INTERVAL '-1 month')) AS timestamp)) AND "public"."spree_orders"."completed_at" < date_trunc('month', CAST(now() AS timestamp)) AND "enterprises__via__distributor_"."sells" = 'any')GROUP BY date_trunc('month', "public"."spree_orders"."completed_at")ORDER BY date_trunc('month', "public"."spree_orders"."completed_at") ASC