Jinja & SQL Quiz

Below you'll find some examples of SQL queries making use of Jinja templates: if you'd like to check what you have learned so far in this tutorial, you can try to write down on your own what each query would look like, once the template has been rendered... and if you are ever in doubt, you can double-check the answer by expanding the corresponding Answer box.

Question 1: Range & loop.last

SELECT
{% for n in range(3) -%}
		{%- if loop.last %}{{ n }} as number_{{ n }}
    {%-else %}
    	{{ n }} as number_{{ n }},
    {%- endif -%}
{% endfor -%}

SELECT
    0 as number_0,
   	1 as number_1, 
	2 as number_2

Question 2:  Loop to column

{%- set values = [ 'Central','South','East','West'] %}
SELECT
{% for value in values -%}
	'{{value}}' as {{ value }},
{% endfor %}

SELECT 
    'Central' as Central,
    'South' as South,
    'East' as East,
    'West' as West,

Question 3:  Loop to column

{%- set date_grains = ["day", "week", "month"] -%}
{%- set states = ["California", "Florida", "Texas"] -%}

{%- for date_grain in date_grains -%}
    SELECT
        'last {{date_grain}}' as grain,
        {%- for state in states %}
        Coalesce(SUM(CASE WHEN state = '{{state}}' THEN sales END),0) AS {{state}}_sales
        {%- if not loop.last -%}
            , 
        {%- endif -%}
        {%- endfor %}
    FROM demo_data.store_orders
    WHERE date_trunc('{{date_grain}}',orderdate) = date_trunc('{{date_grain}}', date('2020-10-30')) 
    GROUP BY 1
    {% if not (loop.last) %}
    UNION ALL
    {% endif %}
{%- endfor %}

SELECT
	'last day' as grain,
    Coalesce(SUM(CASE WHEN state = 'California' THEN sales END),0) AS California_sales,
    Coalesce(SUM(CASE WHEN state = 'Florida' THEN sales END),0) AS Florida_sales,
    Coalesce(SUM(CASE WHEN state = 'Texas' THEN sales END),0) AS Texas_sales
FROM demo_data.store_orders
WHERE date_trunc('day',orderdate) = date_trunc('day', date('2020-10-30'))
GROUP BY 1
UNION ALL
SELECT
    'last week' as grain,
    Coalesce(SUM(CASE WHEN state = 'California' THEN sales END),0) AS California_sales,
    Coalesce(SUM(CASE WHEN state = 'Florida' THEN sales END),0) AS Florida_sales,
    Coalesce(SUM(CASE WHEN state = 'Texas' THEN sales END),0) AS Texas_sales
FROM demo_data.store_orders
WHERE date_trunc('week',orderdate) = date_trunc('week', date('2020-10-30')) 
GROUP BY 1
SELECT
	'last month' as grain,
    Coalesce(SUM(CASE WHEN state = 'California' THEN sales END),0) AS California_sales,
    Coalesce(SUM(CASE WHEN state = 'Florida' THEN sales END),0) AS Florida_sales,
    Coalesce(SUM(CASE WHEN state = 'Texas' THEN sales END),0) AS Texas_sales
FROM demo_data.store_orders
WHERE date_trunc('month',orderdate) = date_trunc('month', date('2020-10-30')) 
GROUP BY 1

Question 4: Rendering with Python

from jinja2 import Template

# Define the SQL query template
query_template = """
    SELECT *
    FROM my_table
    WHERE date BETWEEN '{{ start_date }}' AND '{{ end_date }}'
"""

# Define the start and end dates for the query
start_date = '2022-01-01'
end_date = '2022-12-31'

# Use Jinja to render the query template with the specified dates
query = Template(query_template).render(start_date=start_date, end_date=end_date)

# Print the generated query
print(query)

SELECT *
FROM my_table
WHERE date BETWEEN '2022-01-01' AND '2022-12-31'