<commit_before>SELECT
        IF('{GROUP_COLUMN}' = 'date_id',day_id,t.month_id) AS {GROUP_COLUMN}, 
        SUM(count_bandwidth) AS bandwidth_consumption,
        IF('{GROUP_COLUMN}' = 'date_id', aggr_storage_mb,
        IF(FLOOR({FROM_DATE_ID}/100) = FLOOR({TO_DATE_ID}/100), (SUM(aggr_storage_mb)/(DATEDIFF({TO_DATE_ID},{FROM_DATE_ID})+1)), 
		IF(t.month_id = FLOOR({FROM_DATE_ID}/100),(SUM(aggr_storage_mb)/(DAY(LAST_DAY(DATE(t.month_id * 100 + 1))) - DAY({FROM_DATE_ID}) + 1)),
        IF(t.month_id = FLOOR({TO_DATE_ID}/100),(SUM(aggr_storage_mb)/DAY({TO_DATE_ID})),(SUM(aggr_storage_mb)/DAY(LAST_DAY(DATE(t.month_id * 100 + 1)))))))) AS average_storage,
        MAX(aggr_storage_mb) AS peak_storage,
        SUM(added_storage_mb) AS added_storage,
        SUM(count_bandwidth) + 
        IF('{GROUP_COLUMN}' = 'date_id', aggr_storage_mb,
        IF(FLOOR({FROM_DATE_ID}/100) = FLOOR({TO_DATE_ID}/100), (SUM(aggr_storage_mb)/(DATEDIFF({TO_DATE_ID},{FROM_DATE_ID})+1)), 
		IF(t.month_id = FLOOR({FROM_DATE_ID}/100),(SUM(aggr_storage_mb)/(DAY(LAST_DAY(DATE(t.month_id * 100 + 1))) - DAY({FROM_DATE_ID}) + 1)),
        IF(t.month_id = FLOOR({TO_DATE_ID}/100),(SUM(aggr_storage_mb)/DAY({TO_DATE_ID})),(SUM(aggr_storage_mb)/DAY(LAST_DAY(DATE(t.month_id * 100 + 1)))))))) AS combined_bandwidth_storage
FROM  kalturadw.dwh_dim_time t LEFT JOIN (
	SELECT
		date_id, FLOOR(date_id/100) month_id, partner_id, IFNULL(count_bandwidth_kb, 0)/1024 count_bandwidth, IFNULL(aggr_storage_mb, 0) aggr_storage_mb, IFNULL(count_storage_mb, 0) added_storage_mb
	FROM
		kalturadw.dwh_hourly_partner_usage
        WHERE
		{OBJ_ID_CLAUSE}
		AND
		date_id BETWEEN {FROM_DATE_ID} AND {TO_DATE_ID}
        ) raw_data ON date_id = day_id
WHERE day_id BETWEEN {FROM_DATE_ID} AND {TO_DATE_ID}
GROUP BY IF('{GROUP_COLUMN}' = 'date_id',day_id,t.month_id) 
LIMIT {PAGINATION_FIRST},{PAGINATION_SIZE}  /* pagination  */ 

<commit_msg>def: 3802 - calculate average by numbers of days in filter time frame instead of number of days in month<commit_after>SELECT
        IF('{GROUP_COLUMN}' = 'date_id',day_id,t.month_id) AS {GROUP_COLUMN}, 
        SUM(count_bandwidth) AS bandwidth_consumption,
        IF('{GROUP_COLUMN}' = 'date_id', aggr_storage_mb,
        IF(FLOOR({FROM_DATE_ID}/100) = FLOOR({TO_DATE_ID}/100), (SUM(aggr_storage_mb)/(DATEDIFF({TO_DATE_ID},{FROM_DATE_ID})+1)), 
		IF(t.month_id = FLOOR({FROM_DATE_ID}/100),(SUM(aggr_storage_mb)/(DAY(LAST_DAY(DATE(t.month_id * 100 + 1))) - DAY({FROM_DATE_ID}) + 1)),
        IF(t.month_id = FLOOR({TO_DATE_ID}/100),(SUM(aggr_storage_mb)/DAY({TO_DATE_ID})),(SUM(aggr_storage_mb)/DAY(LAST_DAY(DATE(t.month_id * 100 + 1)))))))) AS average_storage,
        MAX(aggr_storage_mb) AS peak_storage,
        SUM(added_storage_mb) AS added_storage,
        SUM(count_bandwidth) + 
        IF('{GROUP_COLUMN}' = 'date_id', aggr_storage_mb,
        IF(FLOOR({FROM_DATE_ID}/100) = FLOOR({TO_DATE_ID}/100), (SUM(aggr_storage_mb)/(DATEDIFF({TO_DATE_ID},{FROM_DATE_ID})+1)), 
		IF(t.month_id = FLOOR({FROM_DATE_ID}/100),(SUM(aggr_storage_mb)/(DAY(LAST_DAY(DATE(t.month_id * 100 + 1))) - DAY({FROM_DATE_ID}) + 1)),
        IF(t.month_id = FLOOR({TO_DATE_ID}/100),(SUM(aggr_storage_mb)/DAY({TO_DATE_ID})),(SUM(aggr_storage_mb)/DAY(LAST_DAY(DATE(t.month_id * 100 + 1)))))))) AS combined_bandwidth_storage
FROM  kalturadw.dwh_dim_time t LEFT JOIN (
	SELECT
		date_id, FLOOR(date_id/100) month_id, partner_id, IFNULL(count_bandwidth_kb, 0)/1024 count_bandwidth, IFNULL(aggr_storage_mb, 0) aggr_storage_mb, IFNULL(count_storage_mb, 0) added_storage_mb
	FROM
		kalturadw.dwh_hourly_partner_usage
        WHERE
		{OBJ_ID_CLAUSE}
		AND
		date_id BETWEEN {FROM_DATE_ID} AND {TO_DATE_ID}
        ) raw_data ON date_id = day_id
WHERE day_id BETWEEN {FROM_DATE_ID} AND {TO_DATE_ID}
GROUP BY IF('{GROUP_COLUMN}' = 'date_id',day_id,t.month_id) 



{'fraction_non_alphanumeric': 0.15490757615204373, 'fraction_numerical': 0.029159073158031762, 'mean_word_length': 5.511864406779661, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2017963', 'n_tokens_mistral': 1731, 'n_tokens_neox': 1560, 'n_words': 209}