segment-users

-- Power Users: top 10% by credits consumed
SELECT *, (initial_credits - credits) AS credits_used
FROM users
ORDER BY credits_used DESC
LIMIT (SELECT COUNT(*) / 10 FROM users);

-- Dormant: was active, silent for 7-30 days
SELECT * FROM users
WHERE last_active_at < NOW() - INTERVAL '7 days'
  AND last_active_at > NOW() - INTERVAL '30 days';

-- At Risk: paying user, usage dropped below half their average
SELECT * FROM users
WHERE plan != 'free'
  AND (initial_credits - credits) < (
    SELECT AVG(initial_credits - credits) * 0.5
    FROM users WHERE plan != 'free'
  );

// Prisma: dormant users
const dormant = await prisma.user.findMany({
  where: {
    lastActiveAt: {
      lt: new Date(Date.now() - 7 * 24 * 60 * 60 * 1000),
      gt: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000),
    },
  },
});

SELECT 'power_users' AS segment, COUNT(*) AS count FROM users WHERE ...
UNION ALL
SELECT 'active'      AS segment, COUNT(*) AS count FROM users WHERE ...
UNION ALL
SELECT 'dormant'     AS segment, COUNT(*) AS count FROM users WHERE ...
UNION ALL
SELECT 'churned'     AS segment, COUNT(*) AS count FROM users WHERE ...

USER SEGMENTS — [project name]
════════════════════════════════════
CURRENT DISTRIBUTION
────────────────────────────────────
Power Users:    [N] users  ([X]%)
Active:         [N] users  ([X]%)
Casual:         [N] users  ([X]%)
Dormant:        [N] users  ([X]%)
Churned:        [N] users  ([X]%)
────────────────────────────────────
Total:          [N] users
════════════════════════════════════

[ ] User model read from codebase — no invented fields
[ ] Usage metric identified and defined
[ ] Only segments with available signals generated
[ ] SQL + ORM queries output for each segment
[ ] Count queries included so distribution is visible
[ ] At Risk segment flagged if any paying users exist
[ ] Action recommendation per segment — specific, not generic
[ ] All column names in queries match the actual schema