---
title: Common Queries
slug: redshift/m8jL-common-queries
icon: {"lucideIcon":"ScanSearch"}
docTags: 
createdAt: 2026-10-01T12:33:23.336Z
---

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:

```sql
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:

```sql
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:

```sql
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):

```sql
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.