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'