Dynamic Count and Export Query Syntax

This syntax is used to format dynamic queries. The base wrapper for the BASEQUERY variable will be curly brackets, and a pipe character

{{
... some query
| BASEQUERY }}

Note that the query inside the BASEQUERY envelope needs to return all the columns that can be used for dynamic counts or exports. In the examples below, the SQL wrapping the base query is getting only a count, even though the query within returns all columns.

Overall Count

The overall counts is used as the top level query for both dynamic counts and dynamic exports, and surrounds the BASEQUERY. In order to use the overall count, it needs to have the label set to “overall” (case insensitive)

Example

This example shows all the components that are part of a base query. This includes

The BASEQUERY annotionation and wrapping curly brackets
Various WHERE clause variables for table-specific where filtering, with where tags (tbl1 and tbl12) are included to note which field filters are used in which where clauses, The field definitions will have the same tags annotated on them
The BDS_SEG_LIMIT, used to set a segment level limit of records. Each segment can have its own limit
The whole limit variable, used to limit rows across all segments
The intra-segment level duduplicaton variable
The inter-segment level deduplication variable
The intra-segment level suppression variable
The inter-segment level suppression variable

SELECT SEGMENT_NAME, COUNT(*) AS "Available Universe", BDS_SEG_N FROM (
{{
WITH
TABLE1_FILTERED AS (
    SELECT PERSON_ID
    FROM PROVIDER_BETTRDATA.PROD.TABLE1_VW
    WHERE {{WHERE.tbl1}}
    QUALIFY ROW_NUMBER() OVER (PARTITION BY {{DEDUPLICATION_COLUMN}} ORDER BY ADVANTAGE_HOME_OWNER DESC NULLS LAST) = 1
),
TABLE2_FILTERED AS (
    SELECT PERSON_ID
    FROM PROVIDER_BETTRDATA.PROD.TABLE2_VW
    WHERE {{WHERE.tbl2|outer}}
),
POST_FILTERED_IDS AS (
    SELECT
    DISTINCT
    EPS.PERSON_ID
  FROM TABLE1_FILTERED EPS
  LEFT JOIN TABLE2_FILTERED EF ON EPS.PERSON_ID = EF.PERSON_ID
  WHERE
  ((select count(*) from TABLE2_FILTERED) = 0 or EF.PERSON_ID is not null)
  limit {{BDS_SEG_LIMIT}}
)
SELECT
EPS.*,
EF.*
FROM PROVIDER_BETTRDATA.PROD.TABLE1_VW EPS
LEFT JOIN PROVIDER_BETTRDATA.PROD.TABLE2_VW EF ON EPS.PERSON_ID = EF.PERSON_ID
WHERE EPS.PERSON_ID IN (SELECT PERSON_ID FROM POST_FILTERED_IDS)
| BASEQUERY }}
) S GROUP BY SEGMENT_NAME, BDS_SEG_N ORDER BY BDS_SEG_N

Thank you