Adding SQL Code via Filter to Nested Section

[clathrop]

I apologize in advance if this is simple. I’m new to LookML and wasn’t able to find anything in the documentation.

I’m working on a derived table that will create a cohort analysis. To do this I have a CTE query with several nested sections. I need to be able to dynamically add a filter to two of these sections.

    SELECT t.division_id,
           t.contact_id                                                                         AS contact_id,
           date_trunc('month', (TIMESTAMP 'epoch' + (MIN(t.date)) * INTERVAL '1 second'))::DATE AS cohort_month
    FROM bi.transactions t
             LEFT JOIN bi.products prod ON t.product_id = prod.ezyvet_id AND prod.location_id = t.location_id
    -- Adds optional filter
    WHERE << I want to add a filter here >>
    GROUP BY 1, 2
    ORDER BY 1, 2
),
-- (user_id, month_number): user X has activity in month number X
     user_activities AS (
         SELECT trans.division_id,
                trans.contact_id,
                MONTHS_BETWEEN(DATE_TRUNC('month', (TIMESTAMP 'epoch' + (trans.date) * INTERVAL '1 second'))::DATE,
                               cohort.cohort_month) AS month_number
         FROM bi.transactions trans
                  LEFT JOIN bi.products prod
                            ON trans.product_id = prod.ezyvet_id AND prod.location_id = trans.location_id
                  LEFT JOIN cohort_items cohort
                            ON trans.contact_id = cohort.contact_id AND trans.division_id = cohort.division_id
         -- Adds optional filter
         WHERE << I want to add a filter here >> 
         GROUP BY 1, 2, 3
     ),
-- (cohort_month, size)
     cohort_size AS (
         SELECT cohort_month,
                division_id,
                count(1) AS num_users
         FROM cohort_items
         GROUP BY 1, 2
         ORDER BY 1, 2
     ),
-- (cohort_month, month_number, cnt)
     month_count AS (
         SELECT users.division_id,
                cohort.cohort_month,
                users.month_number,
                count(1) AS num_users
         FROM user_activities users
                  LEFT JOIN cohort_items cohort ON users.contact_id = cohort.contact_id AND users.division_id = cohort.division_id
         GROUP BY 1, 2, 3
     )
-- our final value: (cohort_month, size, month_number, percentage)
SELECT month_count.division_id,
       month_count.cohort_month,
       month_count.month_number,
       cohort.num_users                                           AS cohort_users,
       month_count.num_users                                      AS month_num_active_users,
       ROUND(month_count.num_users::FLOAT * 100 / cohort.num_users, 2) AS percentage
FROM month_count
         LEFT JOIN cohort_size cohort ON month_count.cohort_month = cohort.cohort_month AND month_count.division_id = cohort.division_id
WHERE month_count.cohort_month IS NOT NULL
  AND month_count.month_number > 0
ORDER BY   1, 2, 3

Using the filter block and sql: will only add the filter to the final rendered SQL statement. I can’t figure out how to give the user a yesno type option to either engage or not engage this filter up near the top of the code where I have << I want to add a filter here >> indicated.

[Cyril_MTL_Analy]

Hello @clathrop and welcome here!

What you want here is probably liquid and templated filters.