<commit_before>-- Aggregate bird_migration_altitude_profile data:
-- - Aggregate on time frame (e.g. 20 min)
-- - Aggregate on altitude band (e.g. 200-1600m, 1600+ m)
-- - Average values

SELECT
  radar_id,
  date_trunc('hour', start_time) + date_part('minute', start_time)::int / 20 * interval '20 min' AS interval_start_time,
  CASE
    WHEN altitude >= 0.2 AND altitude < 1.6 THEN 'low'
    WHEN altitude >= 1.6 THEN 'high'
  END AS altitude_band,
  count(*) AS number_of_measurements,
  avg(u_speed) AS avg_u_speed,
  avg(v_speed) AS avg_v_speed,
  avg(bird_density) AS avg_bird_density
FROM
  bird_migration_altitude_profiles
WHERE
  radial_velocity_std >= 2
  AND bird_density >= 10
  AND altitude >= 0.2
GROUP BY
  radar_id,
  interval_start_time,
  altitude_band
ORDER BY
  radar_id,
  interval_start_time,
  altitude_band DESC<commit_msg>Update SQL with WITH statement<commit_after>-- Aggregate bird_migration_altitude_profile data:
-- - Aggregate on time frame (e.g. 20 min)
-- - Aggregate on altitude band (e.g. 200-1600m, 1600+ m)
-- - Average values

WITH aggregated_data AS (
    SELECT
        radar_id,
        date_trunc('hour', start_time) + date_part('minute', start_time)::int / 20 * interval '20 min' AS interval_start_time,
        CASE
            WHEN altitude >= 0.2 AND altitude < 1.6 THEN 'low'
            WHEN altitude >= 1.6 THEN 'high'
        END AS altitude_band,
        count(*) AS number_of_measurements,
        avg(u_speed) AS avg_u_speed,
        avg(v_speed) AS avg_v_speed,
        avg(bird_density) AS avg_bird_density
    FROM
        bird_migration_altitude_profiles
    WHERE
        radial_velocity_std >= 2
        AND bird_density >= 10
        AND altitude >= 0.2
    GROUP BY
        radar_id,
        interval_start_time,
        altitude_band
    ORDER BY
        radar_id,
        interval_start_time,
        altitude_band DESC
)

SELECT
    *
FROM
    aggregated_data
{'fraction_non_alphanumeric': 0.0858480749219563, 'fraction_numerical': 0.029136316337148804, 'mean_word_length': 2.8848484848484848, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 13, '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': '2247419', 'n_tokens_mistral': 729, 'n_tokens_neox': 658, 'n_words': 177}