SQL多子查询动态日期匹配及格式转换问题求助

Here's the revised SQL code that addresses both problems, allowing you to specify a qualifying month and dynamically calculate user groups for each target month:

WITH QualifyingMonthParam AS (
  -- Replace 'Jan24' with your desired qualifying month (format: MonYY)
  SELECT 'Jan24' AS qualifying_month_str
),
TargetMonths AS (
  SELECT
    q.qualifying_month_str,
    -- Convert qualifying month to first day of the month
    TO_DATE(q.qualifying_month_str, 'MonYY') AS qualifying_month_date,
    -- Calculate target months: 8,7,6 months before qualifying month (adjust offsets if needed)
    DATEADD(MONTH, -8, TO_DATE(q.qualifying_month_str, 'MonYY')) AS target_month1_date,
    DATEADD(MONTH, -7, TO_DATE(q.qualifying_month_str, 'MonYY')) AS target_month2_date,
    DATEADD(MONTH, -6, TO_DATE(q.qualifying_month_str, 'MonYY')) AS target_month3_date,
    -- Format target months as Mon-YYYY for display
    TO_CHAR(DATEADD(MONTH, -8, TO_DATE(q.qualifying_month_str, 'MonYY')), 'Mon-YYYY') AS target_month1_str,
    TO_CHAR(DATEADD(MONTH, -7, TO_DATE(q.qualifying_month_str, 'MonYY')), 'Mon-YYYY') AS target_month2_str,
    TO_CHAR(DATEADD(MONTH, -6, TO_DATE(q.qualifying_month_str, 'MonYY')), 'Mon-YYYY') AS target_month3_str
  FROM QualifyingMonthParam q
),
TargetMonthsList AS (
  -- Unpivot target months into a single list for easier filtering and joins
  SELECT target_month_date AS month_start_date, target_month1_str AS month_str FROM TargetMonths
  UNION ALL
  SELECT target_month2_date AS month_start_date, target_month2_str AS month_str FROM TargetMonths
  UNION ALL
  SELECT target_month3_date AS month_start_date, target_month3_str AS month_str FROM TargetMonths
),
ClaimsData AS (
  SELECT
    cb.claim_status_last_updated_date,
    cb.user_id,
    cb.partner_validation_status,
    -- Get first day of the claim's month for user group matching
    DATE_TRUNC('MONTH', cb.claim_status_last_updated_date) AS claim_month_start,
    TO_CHAR(cb.claim_status_last_updated_date, 'Mon-YYYY') AS claim_month_str
  FROM claims_basic cb
  JOIN TargetMonthsList tml
    -- Filter claims using date ranges (more efficient than TO_CHAR)
    ON cb.claim_status_last_updated_date >= tml.month_start_date
    AND cb.claim_status_last_updated_date < DATEADD(MONTH, 1, tml.month_start_date)
),
PerMonthCustomers AS (
  -- Customers for each month: users with validation update before month start
  SELECT
    tml.month_start_date,
    ula.source_user_id
  FROM user_last_activity ula
  JOIN TargetMonthsList tml
    ON ula.last_partner_validation_status_update_date < tml.month_start_date
  GROUP BY tml.month_start_date, ula.source_user_id
),
PerMonthQualifiedKnownUsers AS (
  -- Known Users for each month: created before month start, not in Customers
  SELECT
    tml.month_start_date,
    ula.source_user_id
  FROM user_last_activity ula
  JOIN TargetMonthsList tml
    ON ula.account_creation_date < tml.month_start_date
  LEFT JOIN PerMonthCustomers pmc
    ON ula.source_user_id = pmc.source_user_id
    AND tml.month_start_date = pmc.month_start_date
  WHERE pmc.source_user_id IS NULL
  GROUP BY tml.month_start_date, ula.source_user_id
)
SELECT
  qml.qualifying_month_str AS Qualifying_Month,
  cd.claim_month_str AS MonthYear,
  COUNT(CASE WHEN pmc.source_user_id IS NOT NULL THEN 1 END) AS "Customer(Total_Claims)",
  COUNT(CASE WHEN pmc.source_user_id IS NOT NULL AND cd.partner_validation_status = 'Qualified For Gift' THEN 1 END) AS "Customer(Qualified_for_Gift)",
  NULLIF(CAST(COUNT(CASE WHEN pmc.source_user_id IS NOT NULL AND cd.partner_validation_status = 'Qualified For Gift' THEN 1 END) * 100.0 / NULLIF(COUNT(CASE WHEN pmc.source_user_id IS NOT NULL THEN 1 END), 0) AS DECIMAL(10, 2)), 0) AS "Customers_percentage",
  COUNT(CASE WHEN pmku.source_user_id IS NOT NULL THEN 1 END) AS "Known_User(Total_Claims)",
  COUNT(CASE WHEN pmku.source_user_id IS NOT NULL AND cd.partner_validation_status = 'Qualified For Gift' THEN 1 END) AS "Known_User(Qualified_for_Gift)",
  NULLIF(CAST(COUNT(CASE WHEN pmku.source_user_id IS NOT NULL AND cd.partner_validation_status = 'Qualified For Gift' THEN 1 END) * 100.0 / NULLIF(COUNT(CASE WHEN pmku.source_user_id IS NOT NULL THEN 1 END), 0) AS DECIMAL(10, 2)), 0) AS "Known_Users_percentage",
  COUNT(CASE WHEN pmc.source_user_id IS NULL AND pmku.source_user_id IS NULL THEN 1 END) AS "Unknown_User(Total_Claims)",
  COUNT(CASE WHEN pmc.source_user_id IS NULL AND pmku.source_user_id IS NULL AND cd.partner_validation_status = 'Qualified For Gift' THEN 1 END) AS "Unknown_User(Qualified_for_Gift)",
  NULLIF(CAST(COUNT(CASE WHEN pmc.source_user_id IS NULL AND pmku.source_user_id IS NULL AND cd.partner_validation_status = 'Qualified For Gift' THEN 1 END) * 100.0 / NULLIF(COUNT(CASE WHEN pmc.source_user_id IS NULL AND pmku.source_user_id IS NULL THEN 1 END), 0) AS DECIMAL(10, 2)), 0) AS "Unknown_Users_percentage"
FROM ClaimsData cd
JOIN TargetMonths qml ON 1=1
LEFT JOIN PerMonthCustomers pmc
  ON cd.user_id = pmc.source_user_id
  AND cd.claim_month_start = pmc.month_start_date
LEFT JOIN PerMonthQualifiedKnownUsers pmku
  ON cd.user_id = pmku.source_user_id
  AND cd.claim_month_start = pmku.month_start_date
GROUP BY qml.qualifying_month_str, cd.claim_month_str
ORDER BY cd.claim_month_str;

Key Fixes: #

• Dynamic Qualifying Month: The QualifyingMonthParam CTE lets you specify any qualifying month (e.g., 'Jan24'). The TargetMonths CTE calculates the three target months relative to this input.
• Date Format Alignment:
  • Claims are filtered using date ranges (instead of TO_CHAR) for better performance and accuracy.
  • PerMonthCustomers and PerMonthQualifiedKnownUsers compute user groups per target month, using the first day of each month (YYYY-MM-DD format) for date comparisons, matching your requirement.
• Dynamic User Group Matching: Each claim is joined to the correct user group for its month via claim_month_start, ensuring the cutoff dates align with the claim's month.

Notes: #

• Adjust the month offsets (-8, -7, -6) in TargetMonths if your target months need to be relative to the qualifying month differently.
• The code uses TO_DATE with 'MonYY' format; adjust this if your SQL dialect uses a different format string (e.g., PostgreSQL uses 'MonYY' but some dialects may need 'MonRR').
• Added NULLIF in denominator calculations to avoid division-by-zero errors.

内容的提问来源于stack exchange，提问作者sourav kumar