Shopify SQL Queries: 15 Templates for Orders and Customers

Quick answer

Revenue, orders and average order value per month, on Shopify data synced by Fivetran into Postgres. Shopify’s admin uses ShopifyQL instead; these templates are for SQL on a synced copy.

-- PostgreSQL (Fivetran Shopify schema "shopify")
-- Revenue, orders and AOV per month
select
  date_trunc('month', created_at)::date as month,
  currency,
  count(*) as orders,
  sum(total_price) as revenue,
  round(avg(total_price), 2) as avg_order_value
from shopify."order"
where not test
  and cancelled_at is null
  and not coalesce(_fivetran_deleted, false)
group by 1, 2
order by 1 desc;

Schema assumptions

Source	Tables used	Notes
Shopify via Fivetran (classic schema)	order (id, customer_id, created_at, cancelled_at, test, currency, total_price, total_discounts, financial_status, source_name), order_line (order_id, product_id, variant_id, quantity, price, total_discount, sku, title), customer (id, email, created_at), product (id, title, product_type, vendor), product_variant (id, product_id, title, sku, inventory_quantity), refund (id, order_id, created_at), order_line_refund (refund_id, order_line_id, quantity, subtotal)	order must be quoted. Fivetran’s newer GraphQL-based schema renames money columns, e.g. total_price_set_shop_amount and currency_code.
Shopify via Airbyte	orders (with line_items and refunds as JSON arrays), customers, products, product_variants, order_refunds	Unnest line_items with jsonb_array_elements in Postgres.
demo_shop (AI2SQL)	customers (id, name, country, signup_date), products (id, name, category, price), orders (id, customer_id, order_date, status, total), order_items (id, order_id, product_id, quantity, unit_price)	Read-only Postgres. status is completed, shipped, pending or cancelled.

All queries below are PostgreSQL on Fivetran’s Shopify schema. Change shopify to your schema name. Example outputs use made-up data.

Copy-ready SQL

Sales

1. Revenue and orders per day, last 30 days

Purpose: the daily pulse. Change: 'America/New_York' to your store timezone. This assumes created_at is timestamptz; if your column is a plain timestamp in UTC, write created_at at time zone 'UTC' at time zone 'America/New_York'.

-- PostgreSQL (Fivetran Shopify schema "shopify")
select
  (created_at at time zone 'America/New_York')::date as day,
  count(*) as orders,
  sum(total_price) as revenue
from shopify."order"
where not test and cancelled_at is null
  and created_at >= now() - interval '30 days'
group by 1
order by 1 desc;

day	orders	revenue
2026-10-11	38	2412.50

2. Average order value per month

Purpose: is basket size growing? Change: nothing.

-- PostgreSQL (Fivetran Shopify schema "shopify")
select
  date_trunc('month', created_at)::date as month,
  round(sum(total_price) / count(*), 2) as avg_order_value,
  round(sum(total_discounts) / count(*), 2) as avg_discount
from shopify."order"
where not test and cancelled_at is null
group by 1
order by 1 desc;

month	avg_order_value	avg_discount
2026-09-01	63.40	4.10

3. Orders by payment status

Purpose: how much is paid, pending, refunded or voided. Change: nothing.

-- PostgreSQL (Fivetran Shopify schema "shopify")
select financial_status, count(*) as orders, sum(total_price) as amount
from shopify."order"
where not test
group by 1
order by orders desc;

financial_status	orders	amount
paid	4120	261204.00
refunded	96	5870.00

4. Revenue by sales channel

Purpose: online store vs POS vs other channels. Change: nothing.

-- PostgreSQL (Fivetran Shopify schema "shopify")
select source_name, count(*) as orders, sum(total_price) as revenue
from shopify."order"
where not test and cancelled_at is null
  and created_at >= now() - interval '90 days'
group by 1
order by revenue desc;

source_name	orders	revenue
web	1180	74800.00
pos	210	9640.00

5. Share of orders using a discount

Purpose: how dependent sales are on discounts. Change: nothing.

-- PostgreSQL (Fivetran Shopify schema "shopify")
select
  date_trunc('month', created_at)::date as month,
  round(100.0 * count(*) filter (where total_discounts > 0) / count(*), 1) as pct_orders_discounted,
  sum(total_discounts) as total_discounts
from shopify."order"
where not test and cancelled_at is null
group by 1
order by 1 desc;

month	pct_orders_discounted	total_discounts
2026-09-01	27.4	1890.00

Products

6. Top products by revenue

Purpose: what actually sells. Revenue per line is price × quantity minus line discounts. Change: the window and limit.

-- PostgreSQL (Fivetran Shopify schema "shopify")
select
  p.title as product,
  sum(ol.quantity) as units,
  sum(ol.price * ol.quantity - ol.total_discount) as revenue
from shopify.order_line ol
join shopify."order" o on o.id = ol.order_id
join shopify.product p on p.id = ol.product_id
where not o.test and o.cancelled_at is null
  and o.created_at >= now() - interval '90 days'
group by 1
order by revenue desc
limit 20;

product	units	revenue
Canvas Tote	412	9888.00

7. Revenue by product type

Purpose: category mix. Change: product_type to vendor for a brand split.

-- PostgreSQL (Fivetran Shopify schema "shopify")
select
  coalesce(nullif(p.product_type, ''), '(none)') as product_type,
  sum(ol.price * ol.quantity - ol.total_discount) as revenue
from shopify.order_line ol
join shopify."order" o on o.id = ol.order_id
join shopify.product p on p.id = ol.product_id
where not o.test and o.cancelled_at is null
group by 1
order by revenue desc;

product_type	revenue
Bags	48210.00

8. Top variants by units sold

Purpose: which size or colour to restock first. Change: the window.

-- PostgreSQL (Fivetran Shopify schema "shopify")
select
  p.title as product,
  v.title as variant,
  v.sku,
  sum(ol.quantity) as units
from shopify.order_line ol
join shopify."order" o on o.id = ol.order_id
join shopify.product_variant v on v.id = ol.variant_id
join shopify.product p on p.id = v.product_id
where not o.test and o.cancelled_at is null
  and o.created_at >= now() - interval '30 days'
group by 1, 2, 3
order by units desc
limit 20;

product	variant	sku	units
Canvas Tote	Navy	TOTE-NVY	141

9. Low-stock variants

Purpose: what runs out next. Change: the threshold of 10.

-- PostgreSQL (Fivetran Shopify schema "shopify")
select p.title as product, v.title as variant, v.sku, v.inventory_quantity
from shopify.product_variant v
join shopify.product p on p.id = v.product_id
where p.status = 'active'
  and v.inventory_quantity < 10
order by v.inventory_quantity;

product	variant	sku	inventory_quantity
Canvas Tote	Olive	TOTE-OLV	2

Customers

10. New vs returning customers per month

Purpose: is growth coming from new buyers or repeat ones? Change: nothing.

-- PostgreSQL (Fivetran Shopify schema "shopify")
with orders as (
  select id, customer_id, created_at, total_price,
         min(created_at) over (partition by customer_id) as first_order_at
  from shopify."order"
  where not test and cancelled_at is null and customer_id is not null
)
select
  date_trunc('month', created_at)::date as month,
  count(distinct customer_id) filter (where created_at = first_order_at) as new_customers,
  count(distinct customer_id) filter (where created_at > first_order_at) as returning_customers,
  sum(total_price) filter (where created_at > first_order_at) as returning_revenue
from orders
group by 1
order by 1 desc;

month	new_customers	returning_customers	returning_revenue
2026-09-01	512	188	14230.00

11. Top customers by spend

Purpose: your VIP list. Change: the limit.

-- PostgreSQL (Fivetran Shopify schema "shopify")
select c.email, count(*) as orders, sum(o.total_price) as spent, max(o.created_at) as last_order_at
from shopify."order" o
join shopify.customer c on c.id = o.customer_id
where not o.test and o.cancelled_at is null
group by c.email
order by spent desc
limit 25;

email	orders	spent	last_order_at
sam@example.com	14	1288.00	2026-10-02 11:41

12. Repeat purchase rate

Purpose: share of customers who ordered more than once. Cohort and LTV versions on the Shopify LTV page. Change: nothing.

-- PostgreSQL (Fivetran Shopify schema "shopify")
with per_customer as (
  select customer_id, count(*) as orders
  from shopify."order"
  where not test and cancelled_at is null and customer_id is not null
  group by customer_id
)
select
  count(*) as customers,
  count(*) filter (where orders > 1) as repeat_customers,
  round(100.0 * count(*) filter (where orders > 1) / nullif(count(*), 0), 1) as repeat_rate_pct
from per_customer;

customers	repeat_customers	repeat_rate_pct
6120	1408	23.0

13. Customers who haven’t ordered in 90 days

Purpose: a win-back list. Change: the 90 days.

-- PostgreSQL (Fivetran Shopify schema "shopify")
select c.email, max(o.created_at) as last_order_at, count(*) as orders
from shopify."order" o
join shopify.customer c on c.id = o.customer_id
where not o.test and o.cancelled_at is null
group by c.email
having max(o.created_at) < now() - interval '90 days'
order by orders desc
limit 100;

email	last_order_at	orders
lee@example.com	2026-05-30 09:12	6

Refunds

14. Refunds per month

Purpose: refunded value over time. Change: nothing.

-- PostgreSQL (Fivetran Shopify schema "shopify")
select
  date_trunc('month', r.created_at)::date as month,
  count(distinct r.id) as refunds,
  sum(olr.subtotal) as refunded_amount
from shopify.refund r
join shopify.order_line_refund olr on olr.refund_id = r.id
group by 1
order by 1 desc;

month	refunds	refunded_amount
2026-09-01	41	2210.00

15. Refund rate by product

Purpose: products that come back. Change: the minimum of 20 units sold.

-- PostgreSQL (Fivetran Shopify schema "shopify")
with sold as (
  select ol.product_id, sum(ol.quantity) as units_sold
  from shopify.order_line ol
  join shopify."order" o on o.id = ol.order_id
  where not o.test and o.cancelled_at is null
  group by 1
),
refunded as (
  select ol.product_id, sum(olr.quantity) as units_refunded
  from shopify.order_line_refund olr
  join shopify.order_line ol on ol.id = olr.order_line_id
  group by 1
)
select
  p.title as product,
  s.units_sold,
  coalesce(r.units_refunded, 0) as units_refunded,
  round(100.0 * coalesce(r.units_refunded, 0) / s.units_sold, 1) as refund_rate_pct
from sold s
join shopify.product p on p.id = s.product_id
left join refunded r on r.product_id = s.product_id
where s.units_sold >= 20
order by refund_rate_pct desc;

product	units_sold	units_refunded	refund_rate_pct
Linen Shirt	230	29	12.6

select
  date_trunc('month', order_date)::date as month,
  count(*) as orders,
  sum(total) as revenue,
  round(avg(total), 2) as avg_order_value
from orders
where status in ('completed', 'shipped')
group by 1
order by 1 desc