Optimized Monthly Membership Report SQL Code

Optimized Snowflake SQL Code

The original SQL query can be optimized for better readability and performance. Key areas of optimization involve simplifying joins, minimizing date comparisons, and avoiding unnecessary table scans. Below is the refined and optimized version of the SQL code:

Optimized Code

WITH CTE_MEMBERSHIP AS (
    SELECT
        "Tenant Id" AS "Tenant_ID",
        "Member Id" AS "Member_ID",
        "Membership Id" AS "Membership_ID",
        MIN("Membership Begin Date") AS "Membership_Open_Date",
        MAX("Membership End Date") AS "Membership_Closed_Date",
        MAX("Membership Opened At Branch ID") AS "Branch_ID"
    FROM
        ACCOUNT_SNAPSHOT
    WHERE
        "Membership Id" IS NOT NULL
    GROUP BY
        "Tenant Id",
        "Member Id",
        "Membership Id"
),

CTE_Membership_EOM AS (
    SELECT
        CTE."Tenant_ID",
        CTE."Member_ID",
        CTE."Membership_ID",
        CTE."Membership_Open_Date",
        CTE."Membership_Closed_Date",
        CTE."Branch_ID",
        d1.YYYYMM AS "Membership_Open_Date_EOM",
        d2.YYYYMM AS "Membership_Closed_Date_EOM"
    FROM
        CTE_MEMBERSHIP CTE
        INNER JOIN CDW_P.CUDX.DIM_DATE d1 ON CTE."Membership_Open_Date" = d1.MY_DATE
        LEFT JOIN CDW_P.CUDX.DIM_DATE d2 ON CTE."Membership_Closed_Date" = d2.MY_DATE
),

FMD AS (
    SELECT
        m.YYYYMM,
        CTE_EOM."Tenant_ID",
        CTE_EOM."Membership_ID",
        CTE_EOM."Membership_Open_Date_EOM",
        CTE_EOM."Membership_Open_Date",
        CTE_EOM."Membership_Closed_Date_EOM",
        CTE_EOM."Membership_Closed_Date",
        CTE_EOM."Branch_ID"
    FROM
        CTE_Membership_EOM CTE_EOM
        INNER JOIN CDW_P.CUDX.DIM_EOM m ON m.YYYYMM >= CTE_EOM."Membership_Open_Date_EOM"
        AND m.YYYYMM <= COALESCE(CTE_EOM."Membership_Closed_Date_EOM", (SELECT YYYYMM FROM CDW_P.CUDX.DIM_DATE WHERE MY_DATE = CURRENT_DATE))
    WHERE
        m.YYYYMM >= 201001
),

FMS AS (
    SELECT
        FMD.YYYYMM,
        FMD."Tenant_ID",
        FMD."Branch_ID",
        COUNT(DISTINCT FMD."Membership_ID") AS "Total_Memberships",
        COUNT_IF(FMD."Membership_Open_Date_EOM" = FMD.YYYYMM) AS "Total_Open",
        COUNT_IF(FMD."Membership_Closed_Date_EOM" = FMD.YYYYMM) AS "Total_Closed"
    FROM
        FMD
    GROUP BY
        FMD.YYYYMM,
        FMD."Tenant_ID",
        FMD."Branch_ID"
)

SELECT
    f.YYYYMM,
    f."Tenant_ID",
    f."Branch_ID",
    f."Total_Memberships",
    f."Total_Open",
    f."Total_Closed",
    (f."Total_Memberships" - f."Total_Closed") AS "Net_Memberships",
    p.YYYYMM AS "Previous_YYYYMM",
    p."Branch_ID" AS "Previous_Branch_ID",
    p."Total_Memberships" AS "Previous_Total_Memberships",
    p."Total_Open" AS "Previous_Total_Open",
    p."Total_Closed" AS "Previous_Total_Closed",
    (p."Total_Memberships" - p."Total_Closed") AS "Previous_Net_Memberships"
FROM
    FMS f
    LEFT JOIN CDW_P.CUDX.DIM_EOM eom ON f.YYYYMM = eom.YYYYMM
    LEFT JOIN FMS p ON p.YYYYMM = eom.previous_yyyymm
    AND COALESCE(f."Branch_ID", '') = COALESCE(p."Branch_ID", '')
    AND f."Tenant_ID" = p."Tenant_ID";

Optimization Details

1. Simplified Joins: Removed redundant INNER JOINs on CURRENT_DATE_VIEW as they were not utilized.
2. Date Comparisons Simplification: Used COALESCE for better readability and performance benefits for date comparisons.
3. Renaming Aliases Consistently: Improved readability with consistent and meaningful alias names.
4. Avoided Unnecessary Table Scans: Reduced multiple table scans by using subqueries and COALESCE.

Practical Example

This optimized SQL can be used to run monthly membership reports in a data analytics platform to track membership trends, openings, and closures for further business analysis.

For further optimization and understanding of such SQL queries, it is recommended to explore the advanced SQL courses offered on the Enterprise DNA Platform.