<commit_before>module.exports = {
  up: `
    CREATE OR REPLACE VIEW birds_migrations_peak_daily_species AS
    SELECT
      DATE_TRUNC('day', observation_date_time) as observation_date,
      season,
      migration_point_en,
      species,
      sum(count) as count
    FROM birds_migrations_observations
    LEFT OUTER JOIN "Users" u ON user_id = u.id
    LEFT OUTER JOIN "Species" s ON "labelLa" = species AND type = 'birds'
    WHERE season is not null
    AND observation_date_time >= DATE_TRUNC('year', CURRENT_DATE - INTERVAL '1 year')
    AND count > 0
    AND (NOT confidential OR confidential IS NULL)
    AND (NOT sensitive OR sensitive IS NULL)
    AND u.privacy = 'public'
    GROUP BY DATE_TRUNC('day', observation_date_time), season, migration_point_en, species
    ORDER BY observation_date DESC
  `,
  down: `
    DROP VIEW IF EXISTS birds_migrations_peak_daily_species
  `
}<commit_msg>feat(migrations): order peak daily species by count within a day<commit_after>module.exports = {
  up: `
    CREATE OR REPLACE VIEW birds_migrations_peak_daily_species AS
    SELECT
      DATE_TRUNC('day', observation_date_time) as observation_date,
      season,
      migration_point_en,
      species,
      sum(count) as count
    FROM birds_migrations_observations
    LEFT OUTER JOIN "Users" u ON user_id = u.id
    LEFT OUTER JOIN "Species" s ON "labelLa" = species AND type = 'birds'
    WHERE season is not null
    AND observation_date_time >= DATE_TRUNC('year', CURRENT_DATE - INTERVAL '1 year')
    AND count > 0
    AND (NOT confidential OR confidential IS NULL)
    AND (NOT sensitive OR sensitive IS NULL)
    AND u.privacy = 'public'
    GROUP BY DATE_TRUNC('day', observation_date_time), season, migration_point_en, species
    ORDER BY observation_date DESC, count DESC
  `,
  down: `
    DROP VIEW IF EXISTS birds_migrations_peak_daily_species
  `
}
{'fraction_non_alphanumeric': 0.07047517351841964, 'fraction_numerical': 0.002135611318739989, 'mean_word_length': 3.5485436893203883, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 7, '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': '3407387', 'n_tokens_mistral': 628, 'n_tokens_neox': 579, 'n_words': 189}