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'