##! experimental.aggregate_order_by

// =============================================================================
// Making the Names Data Sing: answer-key model
//
// U.S. Social Security Administration baby names by state, 1910–2021.
// One row = one (state, sex, year, name) with the number of babies.
// Names given to fewer than 5 babies in a state and year are not included.
// =============================================================================

#" Census Bureau region and division for each state (plus DC).
source: regions is duckdb.table('data/state_regions.csv') extend {
  primary_key: state_code
}

#" Baby names by state, sex and year.
source: names is duckdb.table('data/usa_names.parquet') extend {
  rename: year_born is `year`
  rename: births is `number`
  rename: sex is gender

  join_one: regions on state = regions.state_code

  dimension:
    #" Decade of birth, e.g. 1990 for 1990–1999.
    decade is floor(year_born / 10) * 10
    #" Last letter of the name, lower case.
    last_letter is lower(substr(name, length(name), 1))

  measure:
    total_births is births.sum()
    name_count is count(name)
}

// ---------- Helpers for 4.5 (before/after an event) ----------

#" Retired Atlantic hurricane names that are also girls' names, with the storm's year.
source: hurricanes is duckdb.table('data/hurricanes.csv')

#" Total girls born each year (the denominator for shares).
source: girls_by_year is names -> {
  where: sex = 'F'
  group_by: year_born
  aggregate: girls_born is total_births
}

#" Girls' names joined to the hurricane list and to the yearly total.
source: hurricane_names is names extend {
  where: sex = 'F'
  join_one: storm is hurricanes on name = storm.storm_name
  join_one: girls is girls_by_year on year_born = girls.year_born
}

// ---------- Helpers for 4.6 and 4.7 (similarity between states) ----------

#" The 500 most common girls' names nationally in 2020.
source: top_girls_2020 is names -> {
  where: year_born = 2020 and sex = 'F'
  group_by: name
  aggregate: total_births
  calculate: national_rank is rank() { order_by: total_births desc }
} -> {
  where: national_rank <= 500
  select: name
}

#" Each state's 2020 girls' births for those 500 names: the state's "profile".
source: state_profile_2020 is names extend {
  join_one: top is top_girls_2020 on name = top.name
} -> {
  where: year_born = 2020 and sex = 'F' and top.name is not null
  group_by: state, name
  aggregate: births_2020 is total_births
}

#" Length of each state's profile vector.
source: state_norms is state_profile_2020 -> {
  group_by: state
  aggregate: norm is sqrt(sum(births_2020 * births_2020))
}

#" Unit-length profiles: cosine similarity is then just a sum of products.
source: state_vectors is state_profile_2020 extend {
  join_one: n is state_norms on state = n.state
  dimension: u is births_2020 / n.norm
}

#" Every state paired with every other state, matched on name.
source: state_pairs is state_vectors extend {
  join_many: other is state_vectors on name = other.name
}

#" Cosine similarity for every pair of states.
source: state_similarity is state_pairs -> {
  where: state != other.state
  select: state, other_state is other.state, name, product is u * other.u
} -> {
  group_by: state, other_state
  aggregate: cosine is product.sum()
} extend {
  join_one: r1 is regions on state = r1.state_code
  join_one: r2 is regions on other_state = r2.state_code
}

// =============================================================================
// Answer-key queries. Click "Run" above any query in VS Code.
// =============================================================================

// 1 Type the columns: How big is the dataset?
query: p1_overview is names -> {
  aggregate:
    row_count is count()
    first_year is min(year_born)
    last_year is max(year_born)
    name_count
    state_count is count(state)
    total_births
}

// 1 Type the columns: One row of the data
query: p1_row is names -> {
  where: state = 'WA' and year_born = 1987 and name = 'Olivia'
  select: name, sex, year_born, state, births
}

// 3.1 Names on their own: Share of babies with a top-10 name, and distinct names, by year
query: q3_1 is names -> {
  group_by: year_born, sex, name
  aggregate: total_births
  calculate:
    name_rank is rank() { partition_by: year_born, sex  order_by: total_births desc }
    year_total is sum_window(total_births) { partition_by: year_born, sex }
} -> {
  group_by: year_born, sex
  aggregate:
    top10_share is total_births.sum() { where: name_rank <= 10 } / year_total.max()
    distinct_names is count()
  order_by: year_born, sex
}

// 3.1 Names on their own: Worksheet example: names needed to cover half of boys born in 2020
query: q3_1_ws is names -> {
  where: year_born = 2020 and sex = 'M'
  group_by: name
  aggregate: total_births
  calculate:
    running_total is sum_cumulative(total_births) { order_by: total_births desc }
    all_boys is sum_window(total_births)
} -> {
  where: running_total - total_births < all_boys / 2
  aggregate: names_to_cover_half is count()
}

// 3.2 Names over time: Biggest climbers into the 2010 top 100 (girls)
query: q3_2 is names -> {
  where: sex = 'F' and (year_born = 1990 | 2010)
  group_by: year_born, name
  aggregate: total_births
  calculate: name_rank is rank() { partition_by: year_born  order_by: total_births desc }
} -> {
  group_by: name
  aggregate:
    rank_1990 is name_rank.max() { where: year_born = 1990 }
    rank_2010 is name_rank.max() { where: year_born = 2010 }
} -> {
  where: rank_2010 <= 100
  select:
    name, rank_1990, rank_2010
    places_climbed is rank_1990 - rank_2010
  order_by: places_climbed desc
  limit: 15
}

// 3.2 Names over time: Fads, classics and comebacks: rank by decade
query: q3_2_b is names -> {
  where: sex = 'F'
  group_by: decade, name
  aggregate: total_births
  calculate: decade_rank is rank() { partition_by: decade  order_by: total_births desc }
} -> {
  where: name = 'Jennifer' | 'Elizabeth' | 'Emma' | 'Hazel' | 'Ava'
  select: name, decade, decade_rank
  order_by: name, decade
}

// 3.3 Names and sex: Biggest change in percent female, 1950s → 2010s
query: q3_3 is names -> {
  where: year_born >= 1950 and year_born < 2020
  group_by: name, decade
  aggregate:
    total_births
    pct_female is coalesce(total_births { where: sex = 'F' }, 0) / total_births
} -> {
  group_by: name
  aggregate:
    decades_with_1000 is count() { where: total_births >= 1000 }
    pct_female_1950s is pct_female.max() { where: decade = 1950 }
    pct_female_2010s is pct_female.max() { where: decade = 2010 }
    first_majority_female is decade.min() { where: pct_female > 0.5 }
    first_majority_male is decade.min() { where: pct_female < 0.5 }
} -> {
  where: decades_with_1000 = 7
  select:
    name, pct_female_1950s, pct_female_2010s
    change is pct_female_2010s - pct_female_1950s
    size_of_change is abs(pct_female_2010s - pct_female_1950s)
    crossed_50_in is
      pick first_majority_female when pct_female_1950s < 0.5 and pct_female_2010s > 0.5
      pick first_majority_male when pct_female_1950s > 0.5 and pct_female_2010s < 0.5
      else null
  order_by: size_of_change desc
  limit: 15
}

// 3.3 Names and sex: Worksheet example: names that went from >80% one sex to >80% the other
query: q3_3_ws is names -> {
  group_by: name, decade
  aggregate:
    total_births
    pct_female is coalesce(total_births { where: sex = 'F' }, 0) / total_births
} -> {
  where: total_births >= 500
  group_by: name
  aggregate:
    first_mostly_boys is decade.min() { where: pct_female < 0.2 }
    last_mostly_boys is decade.max() { where: pct_female < 0.2 }
    first_mostly_girls is decade.min() { where: pct_female > 0.8 }
    last_mostly_girls is decade.max() { where: pct_female > 0.8 }
    first_majority_girls is decade.min() { where: pct_female > 0.5 }
    first_majority_boys is decade.min() { where: pct_female < 0.5 }
} -> {
  where: (first_mostly_boys < last_mostly_girls and first_mostly_boys < first_mostly_girls)
      or (first_mostly_girls < last_mostly_boys and first_mostly_girls < first_mostly_boys)
  select:
    name
    direction is pick 'Boys → girls' when first_mostly_boys < first_mostly_girls else 'Girls → boys'
    crossed_50_in is pick first_majority_girls when first_mostly_boys < first_mostly_girls else first_majority_boys
  order_by: direction, crossed_50_in
}

// 3.4 Names and places: Each state's signature girls' name, 2010
query: q3_4 is names -> {
  where: year_born = 2010 and sex = 'F'
  group_by: state, name
  aggregate:
    total_births
    state_share is total_births / all(total_births, state)
    national_share is all(total_births, name) / all(total_births)
} -> {
  where: total_births >= 100
  group_by: state, name, total_births
  aggregate: over_index is max(state_share / national_share)
  calculate: state_rank is rank() { partition_by: state  order_by: over_index desc }
} -> {
  where: state_rank = 1
  select: state, name, total_births, over_index
  order_by: over_index desc
}

// 3.5 Boys vs girls over time: Names needed to cover half of babies, by decade
query: q3_5 is names -> {
  group_by: decade, sex, name
  aggregate: total_births
  calculate:
    running_total is sum_cumulative(total_births) { partition_by: decade, sex  order_by: total_births desc }
    decade_total is sum_window(total_births) { partition_by: decade, sex }
} -> {
  where: running_total - total_births < decade_total / 2
  group_by: decade, sex
  aggregate: names_to_cover_half is count()
  order_by: decade, sex
}

// 3.6 States over time: Different #1 names across the states, each year
query: q3_6 is names -> {
  group_by: year_born, sex, state, name
  aggregate: total_births
  calculate: state_rank is rank() { partition_by: year_born, sex, state  order_by: total_births desc }
} -> {
  where: state_rank = 1
  group_by: year_born, sex
  aggregate: different_no1_names is count(name)
  nest: most_common_no1 is {
    group_by: name
    aggregate: states is count(state)
    limit: 1
  }
  order_by: year_born, sex
}

// 3.7 The name itself as data: Boys' names ending in “n” and girls' names ending in “a”
query: q3_7 is names -> {
  group_by: decade
  aggregate:
    boys_ending_n is total_births { where: sex = 'M' and last_letter = 'n' } / total_births { where: sex = 'M' }
    girls_ending_a is total_births { where: sex = 'F' and last_letter = 'a' } / total_births { where: sex = 'F' }
  calculate: n_change_from_last_decade is boys_ending_n - lag(boys_ending_n)
  order_by: decade
}

// 4.1 Cohorts: Years in the top 100, by entry cohort
query: q4_1 is names -> {
  group_by: sex, year_born, name
  aggregate: total_births
  calculate: name_rank is rank() { partition_by: sex, year_born  order_by: total_births desc }
} -> {
  where: name_rank <= 100
  group_by: sex, name, year_born
  calculate: entry_year is min_window(year_born) { partition_by: sex, name }
} -> {
  group_by: sex, name, entry_year
  aggregate:
    years_in_top100_first_20 is count() { where: year_born < entry_year + 20 }
    years_in_top100_ever is count()
} -> {
  where: entry_year >= 1950 and entry_year < 1960 or entry_year >= 1980 and entry_year < 1990
  group_by:
    cohort is pick '1950s' when entry_year < 1960 else '1980s'
    sex
  aggregate:
    names_entering is count()
    avg_years_first_20 is years_in_top100_first_20.avg()
    avg_years_ever is years_in_top100_ever.avg()
    pct_10_plus_years is count() { where: years_in_top100_first_20 >= 10 } / count()
  order_by: sex, cohort
}

// 4.2 Survival: Survival curve: share of entering names still in the top 10
query: q4_2 is names -> {
  where: sex = 'F'
  group_by: year_born, name
  aggregate: total_births
  calculate: name_rank is rank() { partition_by: year_born  order_by: total_births desc }
} -> {
  where: name_rank <= 10
  group_by: name, year_born
  calculate: entry_year is min_window(year_born) { partition_by: name }
} -> {
  where: entry_year > 1910 and entry_year <= 2001 and year_born <= entry_year + 20
  group_by:
    era is pick 'Entered 1911–1979' when entry_year < 1980 else 'Entered 1980–2001'
    years_after_entry is year_born - entry_year
  aggregate: names_in_top10 is count()
  calculate: names_entering is first_value(names_in_top10) { partition_by: era  order_by: years_after_entry }
  order_by: era, years_after_entry
} -> {
  select: era, years_after_entry, names_in_top10, names_entering
    still_in_top10 is names_in_top10 / names_entering
  order_by: era, years_after_entry
}

// 4.3 Rank trajectories: New names in the top 10, each year
query: q4_3 is names -> {
  group_by: sex, year_born, name
  aggregate: total_births
  calculate: name_rank is rank() { partition_by: sex, year_born  order_by: total_births desc }
} -> {
  where: name_rank <= 10
  group_by: sex, name, year_born
  calculate: previous_top10_year is lag(year_born) { partition_by: sex, name  order_by: year_born }
} -> {
  where: year_born >= 1920
  group_by: sex, year_born
  aggregate: new_to_top10 is count() { where: previous_top10_year is null or previous_top10_year != year_born - 1 }
  order_by: sex, year_born
}

// 4.3 Rank trajectories: Average new top-10 names per year, by decade
query: q4_3_b is names -> {
  group_by: sex, year_born, name
  aggregate: total_births
  calculate: name_rank is rank() { partition_by: sex, year_born  order_by: total_births desc }
} -> {
  where: name_rank <= 10
  group_by: sex, name, year_born
  calculate: previous_top10_year is lag(year_born) { partition_by: sex, name  order_by: year_born }
} -> {
  where: year_born >= 1920
  group_by: sex, year_born
  aggregate: new_to_top10 is count() { where: previous_top10_year is null or previous_top10_year != year_born - 1 }
} -> {
  group_by: decade is floor(year_born / 10) * 10
  aggregate:
    boys_new_per_year is new_to_top10.avg() { where: sex = 'M' }
    girls_new_per_year is new_to_top10.avg() { where: sex = 'F' }
  order_by: decade
}

// 4.4 Name lifecycles (process mining): Most common lifecycle variants (girls' names that reached the top 10)
query: q4_4 is names -> {
  where: sex = 'F' and year_born < 2020
  group_by: decade, name
  aggregate: total_births
  calculate: decade_rank is rank() { partition_by: decade  order_by: total_births desc }
} -> {
  group_by:
    name, decade, decade_rank
    stage is decade_rank ?
      pick 'Top 10' when <= 10
      pick 'Top 100' when <= 100
      pick 'Top 1000' when <= 1000
      else 'Rare'
  calculate:
    previous_stage is lag(stage) { partition_by: name  order_by: decade }
    best_rank_ever is min_window(decade_rank) { partition_by: name }
} -> {
  where: best_rank_ever <= 10 and (previous_stage is null or previous_stage != stage)
  group_by: name
  aggregate:
    first_decade is decade.min()
    path is string_agg(stage, ' → ') { order_by: decade }
} -> {
  group_by: variant is concat(pick 'Absent → ' when first_decade > 1910 else '', path)
  aggregate:
    names is count()
    examples is string_agg(name, ', ') { order_by: name }
  order_by: names desc
  limit: 8
}

// 4.4 Name lifecycles (process mining): Transitions between stages (the directly-follows graph)
query: q4_4_c is names -> {
  where: sex = 'F' and year_born < 2020
  group_by: decade, name
  aggregate: total_births
  calculate: decade_rank is rank() { partition_by: decade  order_by: total_births desc }
} -> {
  group_by:
    name, decade, decade_rank
    stage is decade_rank ?
      pick 'Top 10' when <= 10
      pick 'Top 100' when <= 100
      pick 'Top 1000' when <= 1000
      else 'Rare'
  calculate:
    previous_stage is lag(stage) { partition_by: name  order_by: decade }
    best_rank_ever is min_window(decade_rank) { partition_by: name }
} -> {
  where: best_rank_ever <= 10 and previous_stage is not null and previous_stage != stage
  group_by: from_stage is previous_stage, to_stage is stage
  aggregate: transitions is count()
  order_by: transitions desc
}

// 4.4 Name lifecycles (process mining): Comebacks: names that entered the top 10 twice
query: q4_4_b is names -> {
  where: sex = 'F' and year_born < 2020
  group_by: decade, name
  aggregate: total_births
  calculate: decade_rank is rank() { partition_by: decade  order_by: total_births desc }
} -> {
  group_by:
    name, decade, decade_rank
    stage is decade_rank ?
      pick 'Top 10' when <= 10
      pick 'Top 100' when <= 100
      pick 'Top 1000' when <= 1000
      else 'Rare'
  calculate:
    previous_stage is lag(stage) { partition_by: name  order_by: decade }
    best_rank_ever is min_window(decade_rank) { partition_by: name }
} -> {
  where: best_rank_ever <= 10 and (previous_stage is null or previous_stage != stage)
  group_by: name
  aggregate:
    times_entering_top10 is count() { where: stage = 'Top 10' }
    path is string_agg(stage, ' → ') { order_by: decade }
} -> {
  where: times_entering_top10 >= 2
  select: name, times_entering_top10, path
}

// 4.5 Before / after an event: Share before and after each storm (per million girls born)
query: q4_5 is hurricane_names -> {
  where: storm.storm_name is not null
  group_by: name, storm_year is storm.storm_year, year_born
  aggregate: per_million_girls is total_births / max(girls.girls_born) * 1000000
} -> {
  group_by: name, storm_year
  aggregate:
    three_years_before is per_million_girls.avg() { where: year_born >= storm_year - 3 and year_born <= storm_year - 1 }
    three_years_after is per_million_girls.avg() { where: year_born >= storm_year + 1 and year_born <= storm_year + 3 }
    years_4_to_6_before is per_million_girls.avg() { where: year_born >= storm_year - 6 and year_born <= storm_year - 4 }
} -> {
  where: three_years_before is not null
  select:
    name, storm_year, three_years_before, three_years_after
    change_after_storm is three_years_after / three_years_before - 1
    change_before_storm is three_years_before / years_4_to_6_before - 1
  order_by: change_after_storm
}

// 4.5 Before / after an event: Katrina, year by year
query: q4_5_b is hurricane_names -> {
  where: name = 'Katrina' and year_born >= 1990
  group_by: year_born
  aggregate: per_million_girls is total_births / max(girls.girls_born) * 1000000
  order_by: year_born
}

// 4.6 Similarity between states: Cosine similarity of each state's 2020 girls' names to Washington's
query: q4_6 is state_similarity -> {
  where: other_state = 'WA'
  group_by: state, region is r1.region
  aggregate: similarity_to_wa is cosine.max()
  order_by: similarity_to_wa desc
}

// 4.7 Networks and clusters: Each state's three most similar states
query: q4_7 is state_similarity -> {
  group_by: state, other_state, region is r1.region, other_region is r2.region
  aggregate: cosine is cosine.max()
  calculate: neighbor_rank is rank() { partition_by: state  order_by: cosine desc }
} -> {
  where: neighbor_rank <= 3
  select: state, region, other_state, other_region, cosine, neighbor_rank
    same_region is region = other_region
  order_by: state, neighbor_rank
}

// 4.7 Networks and clusters: How many of those links stay inside the state's Census region?
query: q4_7_b is state_similarity -> {
  group_by: state, other_state, region is r1.region, other_region is r2.region
  aggregate: cosine is cosine.max()
  calculate: neighbor_rank is rank() { partition_by: state  order_by: cosine desc }
} -> {
  where: neighbor_rank <= 3
  group_by: region
  aggregate:
    neighbor_links is count()
    pct_same_region is count() { where: region = other_region } / count()
  order_by: pct_same_region desc
}

// 4.8 Hierarchy: states inside regions: Wyatt (boys), 2000 vs. 2015, by Census region
query: q4_8 is names -> {
  where: name = 'Wyatt' and sex = 'M'
  group_by: region is regions.region
  aggregate:
    births_2000 is total_births { where: year_born = 2000 }
    births_2015 is total_births { where: year_born = 2015 }
} -> {
  select:
    region, births_2000, births_2015
    increase is births_2015 - births_2000
    growth is births_2015 / births_2000
  calculate: share_of_national_increase is increase / sum_window(increase)
  order_by: increase desc
}