# Jinja & SQL examples

Welcome to the very end of the Jinja-SQL Tutorial.
If you

## Question 1: Range & loop.last

``````{%- 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
``````

``````# Import the JINJA library
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'
``````