Más plantillas para consultas

Más plantillas SQL para extender el dashboard custom o cubrir otros casos de uso analíticos sobre el modelo. Cada bloque tiene un objetivo claro y un fragmento listo para adaptar.

Dialecto SQL

Los snippets están escritos en SQL estándar (estilo PostgreSQL) porque es la sintaxis más expresiva y legible para ejemplos. Funciones específicas como date_trunc, INTERVAL '90 days' o cláusulas FILTER (WHERE …) pueden necesitar ajustes menores según el motor o el dialecto del SQL donde se ejecuten (Snowflake, BigQuery, Redshift, MySQL, etc.). El modelo de datos no cambia; sólo cambian las funciones que se usan para agregarlo.

Approval rate per month and gateway

Track approval health over time, split by gateway. Useful to detect a degraded provider.

SELECT date_trunc('month', p.created_at) AS month,
       g.provider,
       COUNT(*) AS attempts,
       SUM(CASE WHEN p.status = 'approved' THEN 1 ELSE 0 END) AS approved,
       1.0 * SUM(CASE WHEN p.status = 'approved' THEN 1 ELSE 0 END) / COUNT(*) AS approval_rate
FROM payments p
LEFT JOIN gateways g ON g.id = p.gateway_id
WHERE p.created_at >= current_date - INTERVAL '6 months'
GROUP BY 1, 2
ORDER BY 1 DESC, attempts DESC;

Top 20 rejection reasons (last 90 days)

Operational dashboard for the recovery team.

SELECT rejection_code,
       provider_rejection_code,
       response_message,
       COUNT(*) AS payments,
       SUM(amount) AS gross_amount
FROM payments
WHERE status = 'rejected'
  AND created_at >= current_date - INTERVAL '90 days'
GROUP BY 1, 2, 3
ORDER BY payments DESC
LIMIT 20;

Customer lifetime value (paid amount)

Per-customer revenue. Foundational for cohort / LTV reporting.

SELECT c.id AS customer_id,
       c.name,
       c.email,
       COUNT(*) FILTER (WHERE p.status = 'approved') AS approved_payments,
       SUM(p.amount) FILTER (WHERE p.status = 'approved') AS gross_collected,
       MIN(p.created_at) FILTER (WHERE p.status = 'approved') AS first_paid_at,
       MAX(p.created_at) FILTER (WHERE p.status = 'approved') AS last_paid_at
FROM customers c
LEFT JOIN payments p ON p.customer_id = c.id
GROUP BY 1, 2, 3
ORDER BY gross_collected DESC NULLS LAST;

Subscriptions cohort retention

How many subscriptions started in month X are still active N months later.

WITH cohorts AS (
  SELECT date_trunc('month', start_date) AS cohort_month,
         id AS subscription_id,
         status
  FROM subscriptions
)
SELECT cohort_month,
       COUNT(*) AS started,
       COUNT(*) FILTER (WHERE status = 'active') AS still_active,
       COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled,
       COUNT(*) FILTER (WHERE status = 'finished') AS finished
FROM cohorts
GROUP BY 1
ORDER BY 1 DESC;

Payment lifecycle (timeline) for a single payment

Recreate the audit log of a payment using `payment_logs`.

SELECT pl.processed_at,
       pl.action,
       pl.rejection_code,
       pl.provider_rejection_code,
       g.provider
FROM payment_logs pl
LEFT JOIN gateways g ON g.id = pl.gateway_id
WHERE pl.payment_id = 'PYa8EJ1DkDnY'
ORDER BY pl.processed_at, pl.created_at;

Recovered payments after retry

Did our retry strategy work? Find payments that were rejected at least once and ultimately approved.

SELECT p.id AS payment_id,
       p.amount,
       p.submissions_count,
       p.status,
       MIN(pl.processed_at) FILTER (WHERE pl.action = 'rejected') AS first_rejection,
       MAX(pl.processed_at) FILTER (WHERE pl.action = 'approved') AS final_approval
FROM payments p
JOIN payment_logs pl ON pl.payment_id = p.id
WHERE p.status = 'approved'
  AND p.submissions_count > 1
  AND p.created_at >= current_date - INTERVAL '30 days'
GROUP BY 1, 2, 3, 4
ORDER BY final_approval DESC;

Active mandates by payment method type

What portion of recurring authorizations are CBU vs card.

SELECT pm.type,
       COUNT(*) AS mandates
FROM mandates m
JOIN payment_methods pm ON pm.id = m.payment_method_id
WHERE (m.expires_at IS NULL OR m.expires_at > now())
GROUP BY 1
ORDER BY mandates DESC;

Hosted-checkout funnel

Conversion through hosted sessions: opened → completed → paid.

SELECT date_trunc('day', s.created_at) AS day,
       s.kind,
       COUNT(*) AS sessions_created,
       COUNT(s.opened_at) AS sessions_opened,
       COUNT(s.completed_at) AS sessions_completed
FROM sessions s
WHERE s.created_at >= current_date - INTERVAL '30 days'
GROUP BY 1, 2
ORDER BY 1 DESC, 2;

All events for a given subscription

Stream of changes to a specific subscription (created, paused, resumed, ...). Useful for debugging or building an activity feed.

SELECT e.created_at,
       e.type,
       e.data
FROM events e
WHERE e.resource = 'subscription'
  AND e.resource_id = 'SBmQ6j9NWxblNv'
ORDER BY e.created_at;