SQL 20250707

import%20marimo%0A%0A__generated_with%20%3D%20%220.17.6%22%0Aapp%20%3D%20marimo.App()%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_()%3A%0A%20%20%20%20import%20marimo%20as%20mo%0A%20%20%20%20return%20(mo%2C)%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20nav_menu%20%3D%20mo.nav_menu(%0A%20%20%20%20%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22%2Findex.html%22%3A%20f%22%7Bmo.icon('lucide%3Ahome')%7D%20Home%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22%2Fnotebooks%2FSQL_20250706.html%22%3A%20f%22%7Bmo.icon('lucide%3Aarrow-big-left')%7D%20Last%20Day%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22%2Fnotebooks%2FSQL_20250708.html%22%3A%20f%22%7Bmo.icon('lucide%3Aarrow-big-right')%7D%20Next%20Day%22%2C%0A%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20orientation%3D%22horizontal%22%2C%0A%20%20%20%20)%0A%20%20%20%20nav_menu%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%20Problem%2017%3A%20Multiphase%20Intervention%20Coverage%0A%0A%20%20%20%20Goal%3A%20Identify%20drugs%20(interventions)%20that%20have%20been%20tested%20in%20at%20least%203%20different%20trial%20phases.%0A%0A%20%20%20%20Requirements%3A%0A%0A%20%20%20%20-%20Use%20the%20AACT%20database.%0A%20%20%20%20-%20Consider%20only%20interventions%20where%20intervention_type%20%3D%20'Drug'.%0A%20%20%20%20-%20For%20each%20intervention%20name%2C%20count%20how%20many%20distinct%20%60phase%60%20values%20it's%20associated%20with.%0A%20%20%20%20-%20Only%20include%20drugs%20tested%20in%203%20or%20more%20distinct%20phases.%0A%20%20%20%20-%20Output%3A%20intervention_name%2C%20phase_count%0A%20%20%20%20-%20Order%20by%20phase_count%20DESC%2C%20then%20intervention_name%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_()%3A%0A%20%20%20%20%22%22%22%0A%20%20%20%20WITH%20drug_by_phase%20AS%20(%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20nct_id%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20INITCAP(interventions.name)%20AS%20intervention_name%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20TRIM(INITCAP(REGEXP_SPLIT_TO_TABLE(phase%2C%20'%2F')))%20AS%20phase%0A%20%20%20%20%20%20%20%20FROM%0A%20%20%20%20%20%20%20%20%20%20%20%20interventions%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20JOIN%20studies%20USING(nct_id)%0A%20%20%20%20%20%20%20%20WHERE%0A%20%20%20%20%20%20%20%20%20%20%20%20LOWER(intervention_type)%20%3D%20'drug'%0A%20%20%20%20%20%20%20%20%20%20%20%20AND%20phase%20IS%20NOT%20NULL%0A%20%20%20%20%20%20%20%20%20%20%20%20AND%20phase%20!%3D%20'NA'%0A%20%20%20%20)%0A%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20intervention_name%2C%0A%20%20%20%20%20%20%20%20COUNT(DISTINCT%20phase)%20AS%20phase_count%0A%20%20%20%20FROM%0A%20%20%20%20%20%20%20%20drug_by_phase%0A%20%20%20%20GROUP%20BY%0A%20%20%20%20%20%20%20%201%0A%20%20%20%20HAVING%0A%20%20%20%20%20%20%20%20COUNT(DISTINCT%20phase)%20%3E%3D%203%0A%20%20%20%20ORDER%20BY%0A%20%20%20%20%20%20%20%202%20DESC%2C%0A%20%20%20%20%20%20%20%201%3B%0A%20%20%20%20%22%22%22%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%20Problem%2018%3A%20Longest%20Operational%20Airport%20Pair%0A%0A%20%20%20%20Goal%3A%20Find%20the%20airport-to-airport%20route%20that%20has%20operated%20flights%20across%20the%20longest%20calendar%20span.%0A%0A%20%20%20%20Requirements%3A%0A%0A%20%20%20%20-%20Use%20the%20%60postgres_air.flight%60%20table.%0A%20%20%20%20-%20Group%20by%20(departure_airport%2C%20arrival_airport).%0A%20%20%20%20-%20For%20each%20pair%2C%20calculate%20the%20range%20between%3A%0A%20%20%20%20%20%20%20%20-%20the%20earliest%20scheduled_departure%0A%20%20%20%20%20%20%20%20-%20the%20latest%20scheduled_arrival%0A%20%20%20%20-%20Output%3A%20departure_airport%2C%20arrival_airport%2C%20span_days%0A%20%20%20%20-%20Only%20include%20pairs%20where%20span_days%20%3E%3D%201000%0A%20%20%20%20-%20Order%20by%20span_days%20DESC%0A%20%20%20%20-%20Limit%20to%20top%2010%20results%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_()%3A%0A%20%20%20%20%22%22%22%0A%20%20%20%20WITH%20span_by_airport_and_route%20AS%20(%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20departure_airport%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20arrival_airport%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20flight_no%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20MAX(CAST(scheduled_arrival%20AS%20date))%20-%20MIN(CAST(scheduled_departure%20AS%20date))%20AS%20span_days%0A%20%20%20%20%20%20%20%20FROM%20flight%0A%20%20%20%20%20%20%20%20GROUP%20BY%0A%20%20%20%20%20%20%20%20%20%20%20%201%2C%202%2C%203%0A%20%20%20%20)%0A%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20departure_airport%2C%0A%20%20%20%20%20%20%20%20arrival_airport%2C%0A%20%20%20%20%20%20%20%20MAX(span_days)%20AS%20span_days%0A%20%20%20%20FROM%0A%20%20%20%20%20%20%20%20span_by_airport_and_route%0A%20%20%20%20GROUP%20BY%0A%20%20%20%20%20%20%20%201%2C%202%0A%20%20%20%20ORDER%20BY%0A%20%20%20%20%20%20%20%203%20DESC%2C%0A%20%20%20%20%20%20%20%201%2C%202%3B%0A%20%20%20%20%22%22%22%0A%20%20%20%20return%0A%0A%0Aif%20__name__%20%3D%3D%20%22__main__%22%3A%0A%20%20%20%20app.run()%0A