Use Cases

Practical, copy-paste SQL for the questions gym operators ask most. Adapt these rather than writing from scratch — they already handle the date-id joins, privacy nulls, reversed contracts, and the "which fact table" decisions.

For the standard business metric definitions (active members, churn, ARPM, and more), see the KPI Reference.

Conventions

• Date filters use CURRENT_DATE arithmetic so they stay correct without hardcoding.
• Table names are unqualified. Add the magicline. schema prefix only if a query errors on an unknown relation.
• Group per-gym results by organization_unit_id → dim_organization_unit for multi-studio operators.
• Exclude is_reversed contracts and account for NULL customer_id in distinct-member counts.

1. How many active members do I have right now?

The simplest correct answer uses the pre-computed status.

SELECT COUNT(*) AS active_members
FROM dim_customer
WHERE customer_status = 'MEMBER'
  AND NOT COALESCE(archived, false)
  AND NOT COALESCE(deceased, false);

Per gym: join fct_contract for the location, or group members by their studio. For a point-in-time count in the past, derive from contracts instead (start ≤ date < end, not reversed).

2. How many new members did I sign this month?

Count main membership contracts that started in the period. Filter to main contracts and exclude reversals.

SELECT COUNT(*) AS new_members
FROM fct_contract c
JOIN dim_date d ON c.start_date_id = d.date_id
JOIN dim_rate  r ON c.rate_id = r.rate_id
WHERE d.year_month = EXTRACT(YEAR FROM CURRENT_DATE) * 100 + EXTRACT(MONTH FROM CURRENT_DATE)
  AND r.contract_type = 'CONTRACT'
  AND NOT COALESCE(c.is_reversed, false);

3. How many members cancelled (churn) last month?

Cancellations effective in the period, from fct_contract (not the deprecated cancellation table).

SELECT COUNT(*) AS cancellations
FROM fct_contract c
JOIN dim_date d ON c.cancellation_date_id = d.date_id
WHERE d.date >= DATEADD(month, -1, DATE_TRUNC('month', CURRENT_DATE))
  AND d.date <  DATE_TRUNC('month', CURRENT_DATE)
  AND NOT COALESCE(c.is_reversed, false);

For reasons, join dim_contract_cancellation_reason on contract_cancellation_reason_id.

4. How often does the average member visit?

Average visits per checked-in member over the last 4 weeks. Exclude privacy-masked rows from the distinct-member count.

SELECT ROUND(COUNT(*)::numeric / NULLIF(COUNT(DISTINCT customer_id), 0), 1) AS visits_per_member_4wk
FROM fct_checkin
WHERE checkin_date_id >= TO_CHAR(CURRENT_DATE - 28, 'YYYYMMDD')::int
  AND customer_id IS NOT NULL;

Caveat

Members with privacy settings are excluded from the per-member denominator.

5. What are my busiest hours / days?

Time of day (from the real timestamp):

SELECT EXTRACT(HOUR FROM checkin_time) AS hour_of_day, COUNT(*) AS visits
FROM fct_checkin
WHERE checkin_date_id >= TO_CHAR(CURRENT_DATE - 90, 'YYYYMMDD')::int
GROUP BY 1
ORDER BY 1;

Day of week (ordered via the numeric key):

SELECT d.weekday_name, COUNT(*) AS visits
FROM fct_checkin ci
JOIN dim_date d ON ci.checkin_date_id = d.date_id
WHERE ci.checkin_date_id >= TO_CHAR(CURRENT_DATE - 90, 'YYYYMMDD')::int
GROUP BY d.weekday_name, d.day_number_of_week
ORDER BY d.day_number_of_week;

6. What's my revenue trend by month?

Money comes from fct_revenue, dated on accounting_date_id.

SELECT d.year_month, SUM(r.amount) AS revenue
FROM fct_revenue r
JOIN dim_date d ON r.accounting_date_id = d.date_id
WHERE d.date >= DATEADD(month, -12, DATE_TRUNC('month', CURRENT_DATE))
GROUP BY d.year_month
ORDER BY d.year_month;

Membership income only: add JOIN dim_revenue_group g ON r.revenue_group_id = g.revenue_group_id and WHERE g.sub_group = 'MEMBERSHIP_FEE'. Late fees: sub_group = 'DUNNING_PENALTY_FEE'. Write-offs: sub_group = 'DERECOGNITION'. Note this is accrued revenue; use fct_revenue_cash_based for cash received.

7. Which classes are most popular?

Bookings per class from the customer-level class events.

SELECT cl.name AS class_name, COUNT(*) AS bookings
FROM fct_class_event e
JOIN dim_class cl ON e.class_id = cl.class_id
WHERE e.event_date_id >= TO_CHAR(CURRENT_DATE - 90, 'YYYYMMDD')::int
GROUP BY cl.name
ORDER BY bookings DESC
LIMIT 20;

If fct_class_event distinguishes bookings from cancellations via a status/property, filter to booked events before counting.

8. How many day passes did I sell, and were they used?

Day passes are CHECKIN_BASED contingents with a single use.

SELECT COUNT(*) AS day_passes_sold,
       SUM(CASE WHEN num_of_usage - COALESCE(remaining_usage, 0) > 0 THEN 1 ELSE 0 END) AS redeemed
FROM dim_purchased_contingent
WHERE contingent_type = 'CHECKIN_BASED'
  AND num_of_usage = 1
  AND validity_type = 'DAY';

For revenue from day passes, go via bridge_revenue_to_purchased_contingent.

9. What's my lead-to-member conversion?

Use fct_lead_lifetime, which records each PROSPECT / MEMBER / FORMER_MEMBER phase.

-- Conceptual: share of leads created in a cohort that reached MEMBER.
SELECT COUNT(DISTINCT CASE WHEN reached_member THEN customer_id END)::numeric
       / NULLIF(COUNT(DISTINCT customer_id), 0) AS conversion_rate
FROM fct_lead_lifetime
WHERE /* cohort window on the lead-start date */ TRUE;

10. Which active members look at-risk (haven't visited recently)?

Active members with no check-in in the last 30 days — a retention / win-back list.

SELECT COUNT(*) AS at_risk_members
FROM dim_customer cu
WHERE cu.customer_status = 'MEMBER'
  AND NOT COALESCE(cu.archived, false)
  AND cu.customer_id NOT IN (
    SELECT customer_id
    FROM fct_checkin
    WHERE checkin_date_id >= TO_CHAR(CURRENT_DATE - 30, 'YYYYMMDD')::int
      AND customer_id IS NOT NULL
  );

To contact them, select the fields you need (e.g. firstname, lastname, email) instead of COUNT(*).

11. What's the age / gender / location mix of my members?

SELECT gender,
       CASE
         WHEN date_of_birth IS NULL THEN 'Unknown'
         WHEN DATEDIFF(year, date_of_birth, CURRENT_DATE) < 25 THEN 'Under 25'
         WHEN DATEDIFF(year, date_of_birth, CURRENT_DATE) < 35 THEN '25-34'
         WHEN DATEDIFF(year, date_of_birth, CURRENT_DATE) < 50 THEN '35-49'
         ELSE '50+'
       END AS age_band,
       COUNT(*) AS members
FROM dim_customer
WHERE customer_status = 'MEMBER'
  AND NOT COALESCE(archived, false)
  AND NOT COALESCE(deceased, false)
GROUP BY 1, 2
ORDER BY 1, 2;

Swap in city or zip for a location breakdown.

---

Reminders when adapting

• Prefer fct_revenue over the estimate price columns on fct_contract.
• Never return fabricated numbers — if a query can't be run, validate against real data first.
• Confirm exact column names against the Data Model before finalizing a query.