Common Queries

4 min

These are starting points, not exact copies of Digitail's internal reports — use them as a base and adjust filters/columns to match what you need. Column names are verified against the current schema; report logic (e.g. exact revenue recognition rules) may differ slightly from what you see in the Digitail app and should be validated against your own numbers before relying on it.

Sales report

Revenue by day, excluding consolidated sales to avoid double-counting:

SELECT
    DATE(s.taxable_at)                         AS sale_date,
    COUNT(DISTINCT s.id)                       AS sale_count,
    CAST(SUM(s.subtotal) AS NUMERIC(18,2))     AS subtotal,
    CAST(SUM(s.discount) AS NUMERIC(18,2))     AS total_discount,
    CAST(SUM(s.tax_amount) AS NUMERIC(18,2))   AS total_tax,
    CAST(SUM(s.total) AS NUMERIC(18,2))        AS total_revenue
FROM sale s
WHERE s.type != 2                 -- exclude consolidated sales
  AND s.deleted_at IS NULL
  AND s.taxable_at IS NOT NULL
  AND s.taxable_at >= DATEADD(day, -30, CURRENT_DATE)
GROUP BY DATE(s.taxable_at)
ORDER BY sale_date;

Revenue by product, joining down to line items:

SELECT
    p.name                                       AS product_name,
    COUNT(*)                                     AS units_sold,
    CAST(SUM(t.total) AS NUMERIC(18,2))          AS product_revenue,
    CAST(SUM(t.cogs) AS NUMERIC(18,2))           AS total_cogs
FROM treatments t
JOIN sale s      ON s.id = t.sale_id
JOIN products p  ON p.id = t.product_id
WHERE s.type != 2
  AND s.deleted_at IS NULL
  AND t.deleted_at IS NULL
  AND s.taxable_at >= DATEADD(day, -30, CURRENT_DATE)
GROUP BY p.name
ORDER BY product_revenue DESC;

Payments report

Payments received by type, with card detail where relevant:

SELECT
    DATE(c.made_at)                              AS payment_date,
    c.type,
    CASE c.type
        WHEN 0 THEN 'None' WHEN 1 THEN 'Cash' WHEN 2 THEN 'Card'
        WHEN 3 THEN 'Bank transfer' WHEN 5 THEN 'Online' WHEN 6 THEN 'Check'
        WHEN 7 THEN 'Other' WHEN 8 THEN 'Client credit' WHEN 9 THEN 'Care credit'
        WHEN 10 THEN 'Auto (owner change)' ELSE 'Unknown'
    END                                           AS payment_type,
    c.card_brand,
    COUNT(*)                                      AS charge_count,
    CAST(SUM(c.amount) AS NUMERIC(18,2))          AS gross_amount,
    CAST(SUM(c.amount_refunded) AS NUMERIC(18,2)) AS refunded_amount,
    CAST(SUM(c.amount - c.amount_refunded) AS NUMERIC(18,2)) AS net_amount
FROM charges c
WHERE c.deleted_at IS NULL
  AND c.made_at >= DATEADD(day, -30, CURRENT_DATE)
GROUP BY DATE(c.made_at), c.type, c.card_brand
ORDER BY payment_date, payment_type;

Outstanding balance (accounts receivable)

Per-client balance as of today, using the clients view (not users — this is clinic-relationship data):

SELECT
    u.name,
    u.email,
    cl.clinic_id,
    CAST(cl.outstanding_balance AS NUMERIC(18,2)) AS outstanding_balance,
    CAST(cl.credit_balance AS NUMERIC(18,2))      AS credit_balance,
    cl.last_visit_date
FROM clients cl
JOIN users u ON u.id = cl.owner_id
WHERE cl.outstanding_balance > 0
ORDER BY outstanding_balance DESC;

outstanding_balance_is_outdated / credit_balance_is_outdated flag rows where the cached balance hasn't recalculated yet — filter those out (= 0 / FALSE) if you need a guaranteed-fresh number rather than the last computed value.