Referência Técnica

CREATE TABLE events (
  event_uuid UUID PRIMARY KEY,
  user_uuid UUID NOT NULL,
  event_type VARCHAR(50) NOT NULL,
  event_ts TIMESTAMP WITH TIME ZONE NOT NULL,
  recorded_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  data JSONB,
  session_uuid UUID,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE user_identities (
  identity_uuid UUID PRIMARY KEY,
  user_uuid UUID NOT NULL REFERENCES events(user_uuid),
  identity_type VARCHAR(50) NOT NULL,
  identity_value VARCHAR(255) NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

SELECT
  event_type,
  COUNT(*) as total
FROM events
WHERE event_ts >= NOW() - INTERVAL '30 days'
GROUP BY event_type
ORDER BY total DESC;

SELECT
  DATE(event_ts) as data,
  COUNT(DISTINCT user_uuid) as usuarios_unicos
FROM events
WHERE event_ts >= NOW() - INTERVAL '7 days'
GROUP BY DATE(event_ts)
ORDER BY data DESC;

SELECT DISTINCT
  e.user_uuid,
  ui.identity_value as email,
  COUNT(e.event_uuid) as total_eventos
FROM events e
JOIN user_identities ui ON e.user_uuid = ui.user_uuid
WHERE ui.identity_type = 'email'
  AND ui.identity_value LIKE '%@example.com'
GROUP BY e.user_uuid, ui.identity_value
ORDER BY total_eventos DESC;