Common queries

4 min

Full client list

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:

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:

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;

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.