---
title: Common queries
slug: redshift/common-queries
icon: {"lucideIcon":"ScanSearch"}
docTags: 
createdAt: 2026-10-01T12:32:53.669Z
---

### Full client list

```sql
SELECT
    u.id,
    u.name,
    u.email,
    u.phone,
    cl.clinic_id,
    cl.last_visit_date
FROM users u
JOIN clients cl ON cl.owner_id = u.id
ORDER BY u.name;
```

### Active client list

"Active" here means at least one visit in the trailing 12 months — adjust the window to match your definition:

```sql
SELECT
    u.id,
    u.name,
    u.email,
    u.phone,
    cl.clinic_id,
    cl.last_visit_date
FROM users u
JOIN clients cl ON cl.owner_id = u.id
WHERE cl.last_visit_date >= DATEADD(month, -12, CURRENT_DATE)
ORDER BY cl.last_visit_date DESC;
```

### Client marketing list

Filters to clients who haven't opted out and includes referral source for segmentation:

```sql
SELECT
    u.id,
    u.name,
    u.email,
    cl.referral_source_type,
    cl.referral_source_details,
    cl.last_visit_date
FROM users u
JOIN clients cl ON cl.owner_id = u.id
WHERE u.email IS NOT NULL
  AND cl.pending = 0
ORDER BY cl.last_visit_date DESC;
```

:::BlockQuote
There's no explicit marketing-opt-out column on `clients` or `users` in this schema. If your clinic tracks consent, it likely lives in your CRM/email platform rather than Redshift — cross-reference before sending anything.
:::