def get_basic_stats_sql(dimensions):
'''
Returns the sql computing the number of transactions,
as well as the total and the average transaction amounts
for the provided list of column names as dimensions.
'''
params = {'dim': '\n , '.join(dimensions)}
return apply_sql_template(_BASIC_STATS_TEMPLATE, params)
def get_preset_stats_sql(dimensions):
'''
Returns the sql computing the number of transactions,
as well as the total and the average transaction amounts
for the provided list of column names as dimensions.
'''
params = {'dimensions': dimensions}
return apply_sql_template(_PRESET_VAR_STATS_TEMPLATE, params)
_BASIC_STATS_TEMPLATE = '''
select
{{ dim | sqlsafe }}
, count(*) as num_transactions
, sum(amount) as total_amount
, avg(amount) as avg_amount
from
transactions
group by
{{ dim | sqlsafe }}
order by total_amount desc
'''
# Apply the template
params = {'dim': 'payment_method'}
print(apply_sql_template(_BASIC_STATS_TEMPLATE, params))
# Multiple columns joined outside of the template:
def get_basic_stats_sql(dimensions):
'''
Returns the sql computing the number of transactions,
as well as the total and the average transaction amounts
for the provided list of column names as dimensions.
'''
params = {'dim': '\n , '.join(dimensions)}
return apply_sql_template(_BASIC_STATS_TEMPLATE, params)
print(get_basic_stats_sql(['store_id', 'payment_method']))