Requêtes — récap recertification KYC

Support de SKILL.md.

run_sql Neon : project jolly-rain-97989621, branch br-rough-tooth-a2jeitjr (main), database bricks. L'outil tape le primary. Agrégat COUNT, plus la liste ci-dessous si echus_pas_outdated > 0 (max 10). Jamais un dump de customers.

WITH recertif AS (
  SELECT
    c."investorIdentityStatus"->>'status' AS identity_status,
    c."investorIdentityStatus"->'update'->>'status' AS update_status,
    timezone('Europe/Paris', (c."investorIdentityStatus"->'update'->>'dueDate')::timestamptz)::date AS due_date
  FROM customers c
  WHERE c."deletedAt" IS NULL
    AND c."investorIdentityStatus" ? 'update'
)
SELECT
  count(*) AS recertif_ouvertes,
  count(*) FILTER (WHERE identity_status = 'verified') AS verified,
  count(*) FILTER (WHERE identity_status = 'outdated') AS outdated,
  count(*) FILTER (WHERE update_status = 'requested') AS requested,
  count(*) FILTER (WHERE update_status = 'underAnalysis') AS under_analysis,
  count(*) FILTER (WHERE update_status = 'declined') AS declined,
  count(*) FILTER (WHERE due_date < timezone('Europe/Paris', now())::date) AS echeance_depassee,
  count(*) FILTER (WHERE due_date < timezone('Europe/Paris', now())::date
                     AND identity_status <> 'outdated') AS echus_pas_outdated,
  count(*) FILTER (WHERE due_date BETWEEN timezone('Europe/Paris', now())::date
                                      AND timezone('Europe/Paris', now())::date + 7) AS echeance_7j,
  count(*) FILTER (WHERE due_date BETWEEN timezone('Europe/Paris', now())::date
                                      AND timezone('Europe/Paris', now())::date + 30) AS echeance_30j
FROM recertif

Échus non outdated (LIMIT 10) :

SELECT
  c.id,
  c.email,
  timezone('Europe/Paris', (c."investorIdentityStatus"->'update'->>'dueDate')::timestamptz)::date AS due_date
FROM customers c
WHERE c."deletedAt" IS NULL
  AND c."investorIdentityStatus" ? 'update'
  AND c."investorIdentityStatus"->>'status' <> 'outdated'
  AND timezone('Europe/Paris', (c."investorIdentityStatus"->'update'->>'dueDate')::timestamptz)::date
      < timezone('Europe/Paris', now())::date
ORDER BY due_date
LIMIT 10