Variables in SQL Don't Work

Get Help
1

Hi Everyone,

I'm having an issue with the variables on SQL queries on Metabase. Here is my query:

SELECT
  *
FROM
  (
    WITH
      CleanedPageLocations AS (
        SELECT
          *,
          CASE
            WHEN INSTR (page_location, '?') > 0 THEN SUBSTR (page_location, 1, INSTR (page_location, '?') - 1)
            ELSE page_location
          END AS cleaned_page_location
        FROM
          dna_prod_stg.stg_ga4__events
        WHERE
          event_name IN ("first_visit", "sign_up")
          AND first_user_touch_timestamp >= "2024-01-01"
          AND user_pseudo_id IS NOT NULL
      ),
      RankedPageLocations AS (
        SELECT
          *,
          SUM(
            CASE
              WHEN LAG (cleaned_page_location) OVER (
                PARTITION BY
                  user_pseudo_id
                ORDER BY
                  event_timestamp ASC
              ) != cleaned_page_location
              OR LAG (cleaned_page_location) OVER (
                PARTITION BY
                  user_pseudo_id
                ORDER BY
                  event_timestamp ASC
              ) IS NULL THEN 1
              ELSE 0
            END
          ) OVER (
            PARTITION BY
              user_pseudo_id
            ORDER BY
              event_timestamp ASC
          ) AS page_group
        FROM
          CleanedPageLocations
      ),
      PageOrderAssignment AS (
        SELECT
          *,
          ROW_NUMBER() OVER (
            PARTITION BY
              user_pseudo_id
            ORDER BY
              event_timestamp ASC
          ) AS page_order
        FROM
          RankedPageLocations
      ),
      FirstSessionCampaign AS (
        SELECT
          event_date,
          user_pseudo_id,
          CASE
            WHEN cleaned_page_location = 'https://test.com/blog/online-advertising' THEN 'Relax'
            WHEN cleaned_page_location = 'https://test.com/blog/online-advertising-tipps' THEN 'Relax2’
            WHEN cleaned_page_location = 'https://test.com/blog/multi-channel-tips' THEN 'Relax3’
            ELSE NULL
          END AS demand_gen_campaign
        FROM
          PageOrderAssignment
        WHERE
          ga_session_number = 1
          AND page_order = 1
      ),
      PropagatedData AS (
        SELECT
          p.*,
          f.demand_gen_campaign,
          MAX(p.test_company_id) OVER (
            PARTITION BY
              p.user_pseudo_id
          ) AS test_company_id_2
        FROM
          PageOrderAssignment p
          LEFT JOIN (
            SELECT
              user_pseudo_id,
              MAX(demand_gen_campaign) AS demand_gen_campaign
            FROM
              FirstSessionCampaign
            GROUP BY
              user_pseudo_id
          ) f ON p.user_pseudo_id = f.user_pseudo_id
      )
    SELECT
      demand_gen_campaign,
      event_date,
      count(DISTINCT user_pseudo_id) AS new_users,
      count(DISTINCT test_company_id) AS mql
    FROM
      PropagatedData
      WHERE demand_gen_campaign = {{dem}}
    GROUP BY
      1,
      2
  )

I'm using {{dem}} as the variable. here is how it is configured:

image
image592×1174 43.1 KB

When I run it, I keep receiving the error: [Databricks]JDBC Not all parameters have been populated. Ans when I check the running SQL, instead of default value, I see a question mark:

.
.
.
FROM
      PropagatedData
      WHERE demand_gen_campaign = ?
    GROUP BY
      1,
      2

How can I solve this problem and run the query successfully with a variable so that I can do the filtering later in a question?

2

I believe the problem here is that you need to also enclose that in squared brackets to mark it as optional. Have you tried that?
So, instead of

SELECT
      demand_gen_campaign,
      event_date,
      count(DISTINCT user_pseudo_id) AS new_users,
      count(DISTINCT test_company_id) AS mql
    FROM
      PropagatedData
      WHERE demand_gen_campaign = {{dem}}
    GROUP BY
      1,
      2
  )

try

SELECT
      demand_gen_campaign,
      event_date,
      count(DISTINCT user_pseudo_id) AS new_users,
      count(DISTINCT test_company_id) AS mql
    FROM
      PropagatedData
      [[WHERE demand_gen_campaign = {{dem}}]]
    GROUP BY
      1,
      2
  )
1 Like