Search

11 results for “ormon”

Follow GOLF @[email protected]
2026-10-17

https://www.fogolf.com/1420680/ormond-beach-residents-frustrated-over-river-bend-golf-courses-future-after-6-year-closure/ Ormond Beach residents frustrated over River Bend Golf Course’s future after 6-year closure #Golf #GolfCourses #OrmondBeach #VolusiaCounty

#volusiacounty #ormondbeach #golfcourses #golf
Follow GOLF @[email protected]
2026-10-17

https://www.fogolf.com/us/16225/ River Bend Golf Course faces uncertain future amid 3 bids #AirportBusinessParkExpansion #CentralFlorida #Channel9 #FederalAviationAdministration #Florida #FloridaGolf #GolfCourseRestoration #Orlando #OrmondBeach #RiverBendGolfCourse #wftv #WftvChannel9

#wftvchannel9 #wftv #riverbendgolfcourse #ormondbeach #orlando #golfcourserestoration
Follow GOLF @[email protected]
2026-10-16

https://www.fogolf.com/1420530/river-bend-golf-course-faces-uncertain-future-amid-3-bids/ River Bend Golf Course faces uncertain future amid 3 bids #AirportBusinessParkExpansion #CentralFlorida #Channel9 #FederalAviationAdministration #florida #Golf #GolfCourseRestoration #GolfNews #orlando #OrmondBeach #RiverBendGolfCourse #wftv #WftvChannel9

#wftvchannel9 #wftv #riverbendgolfcourse #ormondbeach #orlando #golfnews
HarDoctor News, il Blog di Carlo Cottone @[email protected]
2026-10-17
I farmaci GLP-1 per l’obesità cambieranno la medicina: ma il SSN riuscirà a permetterseli?

Efficacia straordinaria, profilo di rischio non trascurabile e un conto per il Servizio Sanitario Nazionale che potrebbe superare svariati miliardi di euro: il caso semaglutide/tirzepatide mette a nudo le contraddizioni del sistema
Una rivoluzione farmacologica con un prezzo molto alto
Quando un farmaco riduce il peso corporeo del 15-22% in meno di 18 mesi – una performance che nessuna molecola dimagrante aveva mai raggiunto prima – è lecito parlare di rivoluzione terapeutica. È questo il caso di semaglutide (agonista del recettore GLP-1) e tirzepatide (doppio agonista GIP/GLP-1), molecole nate per il diabete di tipo 2 e ora approvate anche nella gestione del peso corporeo in soggetti obesi o sovrappeso con comorbilità. Il dibattito clinico e politico che ne consegue è di straordinaria complessità: da un lato la promessa di ridurre il peso in fasce di pazienti per cui nulla funzionava davvero; dall’altro, una domanda che i sistemi sanitari di tutto il mondo stanno cercando di rispondere: chi paga? e per quanto tempo?
In Italia, come in gran parte d’Europa, la situazione è chiara nei contorni ma nebulosa nelle prospettive: nel 2026 questi farmaci sono rimborsati dal SSN esclusivamente nell’indicazione diabetologica (Nota AIFA 100), mentre per la gestione del peso restano in classe C, a totale carico del cittadino, con costi mensili a dosaggio pieno che oscillano tra 200 e 500 euro. Una barriera economica che, di fatto, trasforma l’accesso a una terapia efficace in un privilegio di censo.
Come funzionano: il meccanismo GLP-1/GIP
Il GLP-1 (glucagon-like peptide-1) è un ormone incretino prodotto dalle cellule L dell’intestino tenue in risposta all’ingestione di cibo. Agisce a livello pancreatico stimolando la secrezione insulinica glucosio-dipendente, a livello ipotalamico riducendo la sensazione di fame e aumentando il senso di sazietà, e a livello gastrico rallentando lo svuotamento. Gli agonisti del recettore GLP-1 ne mimano l’azione farmacologicamente, con emivite prolungate che ne consentono la somministrazione settimanale.
La tirzepatide agisce in modo ancora più articolato, coinvolgendo anche il recettore del gastric inhibitory polypeptide (GIP): questo doppio agonismo si traduce in un ulteriore potenziamento dell’effetto anoressizzante e in una riduzione del peso superiore rispetto ai GLP-1 monoagonisti, come dimostrato nello studio head-to-head SURMOUNT-5.
L’efficacia: dati clinici inconfutabili
I dati degli studi registrativi sono difficilmente controvertibili. Nello studio STEP 1 (Wilding et al., 2021, New England Journal of Medicine), condotto su 1.961 adulti obesi senza diabete (BMI medio 38), la semaglutide 2,4 mg settimanale ha prodotto una riduzione del peso corporeo del 14,9% a 68 settimane, rispetto al 2,4% del placebo. L’83% dei trattati ha raggiunto una perdita ≥5% del peso basale, soglia considerata clinicamente significativa.
Per la tirzepatide, lo studio SURMOUNT-1 (Jastreboff et al., 2022, NEJM) – 2.539 pazienti, BMI medio 38, senza diabete – ha documentato riduzioni di peso del 16,0%, 21,4% e 22,5% rispettivamente con i dosaggi di 5, 10 e 15 mg settimanali, contro il 2,4% del placebo. In media, l’89-96% dei trattati ha perso almeno il 5% del peso iniziale.
Questi risultati si accompagnano a benefici metabolici concreti:
Riduzione pressoria: circa −5 mmHg di pressione arteriosa sistolica media rispetto al placebo.
Protezione cardiovascolare: nello studio SELECT (Lincoff et al., 2023, NEJM), condotto su pazienti con BMI medio 33 e pregresso evento cardiovascolare, la semaglutide ha ridotto il rischio combinato di morte cardiovascolare, infarto o ictus dal 8,0% (placebo) al 6,5% (semaglutide) in 36 mesi di follow-up.
Riduzione dell’incidenza di diabete: nei soggetti inizialmente non diabetici, l’uso prolungato di GLP-1 ha dimostrato una significativa riduzione del rischio di sviluppare diabete di tipo 2.
Nella confronto diretto testa-a-testa dello studio SURMOUNT-5 (NEJM, 2025), la tirzepatide ha prodotto una perdita di peso del 20,2% contro il 13,7% della semaglutide a 72 settimane, confermando la superiorità del doppio agonismo GIP/GLP-1 in termini di riduzione ponderale pura.
Il nodo critico: cosa succede quando si smette?
Uno degli aspetti più problematici della terapia con GLP-1 è la sua strutturale dipendenza dalla continuità. I pazienti raggiungono un plateau di riduzione ponderale entro 12-18 mesi di trattamento continuativo. Alla sospensione, il peso recupera rapidamente: in media, due terzi del peso perso viene riacquistato entro 12 mesi, con ritorno al peso basale entro circa 18 mesi dalla sospensione (West et al., 2026, BMJ). Parallelamente scompaiono i benefici cardiometabolici.
La conseguenza logica – e quella su cui convergono le principali linee guida, inclusa la nuova raccomandazione OMS del 2025 – è che si tratta di una terapia cronica, assimilabile nella sua logica all’antiipertensivo o alla statina. Ma questa analogia è esattamente ciò che rende il problema della sostenibilità sistemica tanto acuto.
Nella pratica clinica, la situazione è ulteriormente complicata dall’alta percentuale di interruzioni precoci: secondo i dati di Do et al. (2024, JAMA Network Open), circa il 50% dei pazienti obesi senza comorbilità maggiori interrompe il trattamento entro 12 mesi dall’inizio, con conseguente effetto yo-yo e perdita di tutti i benefici ottenuti.
Profilo di sicurezza: efficacia non esente da rischi
I GLP-1 non sono privi di effetti avversi rilevanti, alcuni dei quali meritano attenzione clinica proattiva:
Gastrointestinali (i più frequenti): la semaglutide ha causato nausea nel 43,9% dei trattati (vs 16,1% placebo), diarrea nel 29,7% (vs 15,9%) e vomito nel 24,5% (vs 6,3%). La tirzepatide mostra profili analoghi con effetto dose-dipendente. Questi sintomi possono determinare disidratazione e deterioramento della funzione renale acuta.
Pancreatite acuta: segnalata nello 0,2% dei trattati con semaglutide negli studi pivotal.
Calcolosi biliare e colecistite: incidenza rispettivamente dell’1,6% e 0,6% con semaglutide, superiore al placebo.
Disturbi visivi – NAION: Hathaway et al. (2024, JAMA Ophthalmology) hanno documentato un incremento dell’incidenza di neuropatia ottica ischemica anteriore non arteritica (NAION) da 0,8% (non assuntori) a 6,7% (assuntori di semaglutide) nel corso di 36 mesi di trattamento.
Degenerazione maculare correlata all’età (AMD): Shor et al. (2025, JAMA Ophthalmology) hanno osservato un raddoppio dell’incidenza di AMD neovascolare nei pazienti diabetici trattati con GLP-1 rispetto ai non trattati.
Fratture ossee: in uno studio condotto su donne ultrasettantacinquenni (Greenway, 2025, NEJM), la semaglutide ha mostrato un aumento di circa 4 volte delle fratture di anca e bacino rispetto al placebo – un dato che richiede attenzione nella prescrizione nelle donne anziane.
La linea guida OMS 2025: equità e counseling come pilastri
A fine 2025, l’Organizzazione Mondiale della Sanità ha pubblicato la sua prima linea guida sull’uso dei GLP-1 nel trattamento dell’obesità negli adulti (Celletti et al., 2025, JAMA). Due le raccomandazioni fondamentali:
I farmaci GLP-1 possono essere utilizzati come trattamento a lungo termine dell’obesità – riconoscendola esplicitamente come malattia cronica recidivante.
Agli individui in terapia con GLP-1 deve essere fornito un counseling strutturato sui cambiamenti comportamentali e degli stili di vita, con obiettivi definiti per dieta, esercizio fisico e valutazione periodica dei progressi.
La linea guida, inoltre, richiama la necessità di garantire equità nell’accesso attraverso criteri trasparenti di priorità, soprattutto in contesti con risorse limitate, e auspica una copertura pubblica dei costi. Un auspicio che, nel contesto italiano, si scontra con vincoli di bilancio che rendono tale prospettiva quanto meno complessa nel breve termine.
La platea italiana: chi avrebbe diritto al rimborso?
Le dimensioni del problema sono plasticamente descrivibili con i dati epidemiologici nazionali. Secondo il sistema di sorveglianza PASSI (Masocco et al., 2023, Bollettino Epidemiologico Nazionale), in Italia circa il 10% della popolazione adulta 18-69 anni è obesa (BMI ≥30), corrispondente a circa 4 milioni di persone, e un ulteriore 33% è in sovrappeso (circa 13 milioni). Applicando i criteri autorizzativi (BMI ≥30 o BMI 27-29,9 con comorbilità), la platea potenzialmente eleggibile si avvicina agli 8 milioni di persone. Escludendo chi ha già diritto al rimborso SSN per concomitante diabete di tipo 2 (circa il 20-25% della platea), restano fuori dalla copertura pubblica oltre 6 milioni di cittadini
La distribuzione dell’obesità è tutt’altro che casuale: la prevalenza passa dal 6,2% tra i laureati al 20,9% tra chi ha al massimo la licenza elementare. Una disuguaglianza che rende il mancato rimborso non solo una questione economica, ma un problema di equità sociale di primo piano.
Il conto per il SSN: quanto costerebbe rimborsare tutti gli eleggibili?
La matematica è impietosa. Anche ipotizzando un costo annuo per paziente di soli 1.000 euro (il costo-die dei GLP-1 nel SSN per il diabete era di 3,46 euro nel 2024, secondo il Rapporto OsMed 2024 di AIFA), estendere il rimborso ai 6 milioni di pazienti non diabetici eleggibili significherebbe un aggravio aggiuntivo per il Servizio Sanitario Nazionale di svariati miliardi di euro annui. Per un SSN già in crisi di sottofinanziamento strutturale, si tratta di una voce di bilancio insostenibile nelle condizioni attuali.
I consumi di semaglutide nel canale SSN hanno già segnato una crescita impressionante: da circa 322.000 confezioni nel 2020 a oltre 4 milioni nel 2024 (+48,9% su base annua solo nell’ultimo anno), con un parallelo boom del canale privato (+78,4% nel 2024 rispetto al 2023 per le sole confezioni di semaglutide). Questi numeri rendono il dibattito sulla rimborsabilità dell’obesità non più rinviabile.
Una possibile strategia graduata di accesso
L’analisi di Traversa, pubblicata su Salute Internazionale nel febbraio 2026 e ampiamente utilizzata come base di questo post, propone un approccio razionale che non è né un “no” ideologico né un “sì” incondizionato al rimborso allargato. La strategia si articola su più livelli:
Fase 1 – Ottimizzare l’esistente: garantire che i pazienti obesi con diabete di tipo 2, già oggi rimborsabili, ricevano anche interventi comportamentali strutturati (che oggi mancano in oltre la metà dei casi).
Fase 2 – Estensione graduale a criteri di gravità: prioritizzare il rimborso per soggetti con BMI >40 o con obesità di II grado e concomitante rischio cardiovascolare elevato, analogamente a quanto avviene per altri farmaci a rimborso condizionato.
Fase 3 – Negoziazione sul prezzo: contestualmente all’entrata in concorrenza di nuovi farmaci (retatrutide, orforglipron) e alle scadenze brevettuali, negoziare con le aziende produttrici una riduzione progressiva del costo per il SSN.
Fase 4 – Integrazione con politiche di popolazione: nessuna strategia farmacologica può efficacemente contrastare l’obesità se non si interviene sui determinanti strutturali: tassazione delle bevande zuccherate, etichettatura nutrizionale efficace, accessibilità di spazi per l’attività fisica, educazione alimentare nelle scuole.
Commento editoriale
Da internista ospedaliero che quotidianamente gestisce pazienti con diabete, obesità e sindrome metabolica, trovo il dibattito sui GLP-1 e il SSN uno dei più complessi – e onesti – della medicina contemporanea. Ed è appunto la sua onestà che mi piace sottolineare: per la prima volta ci troviamo di fronte a farmaci che funzionano davvero per una condizione, l’obesità, storicamente considerata dalla medicina mainstream un problema di “forza di volontà” più che di biologia.
Ma funzionare non basta. Tre aspetti mi preoccupano seriamente:
Il rebound alla sospensione non è un effetto collaterale tollerabile: è una caratteristica strutturale che trasforma questi farmaci in una terapia vitalizia per milioni di persone. Prescriverli senza un piano di continuità a lungo termine è, a mio avviso, clinicamente irresponsabile. Nessun medico comincerebbe una statina sapendo che il paziente la prenderà solo per un anno.
I segnali oftalmologici (NAION, AMD) devono entrare in modo esplicito nel counseling pre-trattamento e nel monitoraggio periodico. La letteratura su questo punto è ancora in maturazione, ma i dati di Hathaway e Shor non possono essere ignorati in una prescrizione responsabile.
Il rischio di medicalizzazione di massa di un fenomeno – l’eccesso ponderale – che ha radici profonde nei determinanti sociali della salute, nell’industria alimentare ultra-processata, nell’inattività strutturale delle città moderne. Se il SSN rimborsasse i GLP-1 per tutti senza affiancarvi politiche di sanità pubblica efficaci, rischieremmo di trasferire enormi risorse pubbliche all’industria farmaceutica senza modificare i driver di fondo dell’epidemia di obesità.
Non si tratta di essere contro i GLP-1. Si tratta di usarli nel quadro giusto: con indicazioni rigorose, follow-up ravvicinato, interventi comportamentali strutturati e – soprattutto – una strategia di sanità pubblica che non si limiti ad aspettare che i cittadini diventino abbastanza malati da avere diritto a un farmaco costoso.
La domanda vera non è “il SSN può permettersi questi farmaci?”.
La domanda vera è: “può permettersi di non ripensare completamente il modo in cui affronta l’obesità?”
I GLP-1 sono una straordinaria opportunità, ma anche un rischio sistemico se non inseriti in una cornice di politica sanitaria coraggiosa.
Celletti, F., Farrar, J., & De Regil, L. (2025). World Health Organization guideline on the use and indications of glucagon-like peptide-1 therapies for the treatment of obesity in adults. JAMA. Advance online publication. https://doi.org/10.1001/jama.2025.24288
Do, D., Lee, T., Peasah, S. K., Good, C. B., Inneh, A., & Patel, U. (2024). GLP-1 receptor agonist discontinuation among patients with obesity and/or type 2 diabetes. JAMA Network Open, 7, e2413172. https://doi.org/10.1001/jamanetworkopen.2024.13172
Greenway, F. L. (2025). Semaglutide and tirzepatide to treat obesity. New England Journal of Medicine, 393, 84–85. https://doi.org/10.1056/NEJMe2507452
Hathaway, J. T., Shah, M. P., Hathaway, D. B., et al. (2024). Risk of nonarteritic anterior ischemic optic neuropathy in patients prescribed semaglutide. JAMA Ophthalmology, 142, 732–739. https://doi.org/10.1001/jamaophthalmol.2024.2296
Huffman, P. (2025). Will Americans ever be able to afford weight loss drugs? BMJ, 391, r2384. https://doi.org/10.1136/bmj.r2384
Jastreboff, A. M., Aronne, L. J., Ahmad, N. N., et al.; SURMOUNT-1 Investigators. (2022). Tirzepatide once weekly for the treatment of obesity. New England Journal of Medicine, 387, 205–216. https://doi.org/10.1056/NEJMoa2206038
Lincoff, A. M., Brown-Frandsen, K., Colhoun, H. M., et al.; SELECT Trial Investigators. (2023). Semaglutide and cardiovascular outcomes in obesity without diabetes. New England Journal of Medicine, 389, 2221–2232. https://doi.org/10.1056/NEJMoa2307563
Masocco, M., Minardi, V., Contoli, B., et al. (2023). Sovrappeso e obesità nella popolazione adulta in Italia: trend temporali, differenze socio-anagrafiche e regionali con focus sulla Regione Campania. Bollettino Epidemiologico Nazionale, 4(1), 1–8. https://www.epicentro.iss.it/ben/2023/1/sovrappeso-obesita-italia
OsMed. (2025). L’uso dei farmaci in Italia. Rapporto nazionale anno 2024. Agenzia Italiana del Farmaco. https://www.aifa.gov.it/documents/20142/3159201/AIFA_Rapporto_OsMed_2024.pdf
Shi, I., Khan, S. S., Yeh, R. W., Ho, J. E., Dahabreh, I. J., & Kazi, D. S. (2025). Semaglutide eligibility across all current indications for US adults. JAMA Cardiology, 10, 96–98. https://doi.org/10.1001/jamacardio.2024.3999
Shor, R., Mihalache, A., Noori, A., et al. (2025). Glucagon-like peptide-1 receptor agonists and risk of neovascular age-related macular degeneration. JAMA Ophthalmology, 143, 587–594. https://doi.org/10.1001/jamaophthalmol.2025.0803
Traversa, G. (2026, 16 febbraio). Quale accesso ai farmaci anti-obesità. Salute Internazionale. https://www.saluteinternazionale.info/2026/02/quale-accesso-ai-farmaci-antiobesita/
West, S., Koutoukidis, D. A., & Jebb, S. A. (2026). Weight loss drugs are effective, but can healthcare systems afford them? BMJ, 392, s24. https://doi.org/10.1136/bmj.s24
Wilding, J. P. H., Batterham, R. L., Calanna, S., et al.; STEP 1 Study Group. (2021). Once-weekly semaglutide in adults with overweight or obesity. New England Journal of Medicine, 384, 989–1002. https://doi.org/10.1056/NEJMoa2032183
#AIFA #determinantiSociali #diabeteTipo2 #equitàInSalute #farmaciAntiObesità #farmaciDimagranti #GLP1 #lineaGuidaOMS2025 #medicinaInterna #nota100 #obesità #politicaDelFarmaco #rimborsabilità #rischioCardiovascolare #SELECTTrial #semaglutide #sostenibilitàSanitaria #SSN #STEP1 #SURMOUNT1 #SURMOUNT5 #tirzepatide
#aifa #determinantisociali #diabetetipo2 #equitainsalute #farmaciantiobesita #farmacidimagranti
Afrique @[email protected]
2026-10-18

https://www.europesays.com/afrique/239048/ Tebboune ordonne une enquête sur le prix des viandes #Algérie #tebboune #viandes

#viandes #tebboune #algerie
marcoboccaccio @marcoboh
2026-10-18

RE: https://mastodon.social/@rsiinfo/117461002000257605
poi c'è il romanzo di Jules Verne https://it.wikipedia.org/wiki/Il_raggio_verde_(romanzo) e il film omonimo di Eric Rohmer https://it.wikipedia.org/wiki/Il_raggio_verde_(film)
Franco Lettore Folle @[email protected]
2026-10-18

Magia, mistero, un grande amore e il compito di salvare il mondo: se amate il #fantasy non potete perdere le avventure della strega Renée e della sua omonima discendente.
https://www.kobo.com/it/it/ebook/dilogia-della-fenice
https://www.ibs.it/dilogia-della-fenice-mistero-della-ebook-lucrezia-ruggeri/e/9791221326123?queryId=39e5bbb58fb039c67182326db60601c2
https://store.streetlib.com/fantascienza-e-fantasy/dilogia-della-fenice-614386/
https://www.amazon.it/dp/B0FRBSQ86G/ref=sr_1_1?__mk_it_IT=%C3%85M%C3%85%C5%BD%C3%95%C3%91&crid=25CV33XIMS02Q&dib=eyJ2IjoiMSJ9.Mjv0Uz6RIZuICFejfLfuDAnkpIq5PbX0IF8ofeEEFoMa7JaVHoINWQU3fftr1c_jCQVPFRYvL5w2tlykaCtXVSaIHjZ_ykxeZQICwd8cjl51TuieAB3bmjVvcJHpz-B4lK3rODb2bfU1x5nKXXBPo0pxiqow2j98QaO5mkiyvBK6A6WYEUkl1ZnUYlb-niJL.4i91qhjSPs9H_gddVd5yk5KumZwXCtMH00gzHSSSbBw&dib_tag=se&keywords=lucrezia+ruggeri&qid=1758112415&sprefix=lucrezia+ruggeri%2Caps%2C174&sr=8-1
#libri #books #ebook #kindle #ibs #amazon #kobo #streetlib

#fantasy #libri #books #ebook #kindle #ibs
RO Brief 🇷🇴 | Romania & World News @[email protected]
2026-10-17

📰 Judecătoarea Adriana Stoicescu: Dragă Domnule Președinte, Dragă Domnule Premier demis. Noaptea, ca hoții (pentru procedură și detalii știți cui să vă adresați) dați o ordonanță de urgență cu articol unic: PE TERITORIUL ROMÂNIEI HOTĂRÂRILE JUDECĂTOREȘTI NU SUNT OBLIGATORII
de Adriana Stoicescu, judecător Dragă Domnule Președinte, Dragă Domnule Premier demis, Pentru că sunt o ființă generoasă (dacă m-ați cunoaște, sigur…
🔗 https://flux24.ro/judecatoarea-adriana-stoicescu-draga-domnule-presedinte-draga-domnule-premier-demis-noaptea-ca-hotii-pentru-procedura-si-detalii-stiti-cui-sa-va-adresati-dati-o-ordonanta-de-urgenta-cu-articol-u/
#Romania #Stiri #News #Actualitati #Presa

#romania #stiri #news #actualitati #presa
Dev Community @[email protected]
2026-10-18
Introduction

Up to now in our SQL class, I could use SELECT, WHERE, GROUP BY and ORDER BY to get simple answers out of a table. This assignment pushed me further. We had to research four topics and show how each one works on our hospital database:
Part 1: Window functions
Part 2: Joins
Part 3: CTEs (Common Table Expressions)
Part 4: SQL Subqueries
In this article I explain each one the way I came to understand it. For every idea, I'll say what it does, why you would use it, show a query I ran on the hospital data, and explain what the result told me. I've also written down the mistakes I made along the way, because those taught me the most.
I ran every query on my local PostgreSQL 13 on Windows, and the results shown are exactly what I got back.

The hospital database I used

The tables live in a schema (a folder for tables) called morning. I ran this first, so I didn't have to type morning. in front of every table name:
SET search_path TO morning;
There are seven tables. The main one is visits, where each row is one visit to the hospital. The other six tables hold the details about each visit.
TableOne row is...Rowsvisitsone visit30patientsone patient25doctorsone doctor4departmentone department4wardsone ward4diagnosisone diagnosis18proceduresone procedure15

The first thing I noticed is that visits doesn't store any names. It only stores ID numbers like patient_id and doctor_id, which point to the other tables. It also stores facts about each visit: the date, the type of visit (Outpatient, Inpatient or Emergency), how many days the patient stayed, the bill in Kenya shillings (total_bill_kes), the satisfaction score, and a few more.
Before writing any queries, I looked through the data and wrote down a few facts that helped me read my results:
There are four doctors, and each one works in one department:
Dr. Brian Mwangi (101): Cardiology (10)
Dr. Sarah Wanjiku (102): General Medicine (20)
Dr. Peter Otieno (103): Orthopedics (30)
Dr. Faith Chebet (104): Pediatrics (40)

All the visits happened between 1 and 15 June 2026, two visits a day.
Five patients came twice: patients 1, 2, 5, 7 and 10.
All 30 visits together were billed KES 488,000.

Assumptions I made

I used the data exactly as it is in the script. I didn't add, remove or fix anything. Names are shown as they're saved, so one doctor appears as DR.Brian Mwangi.
"Week 1" means 1 to 7 June. "Week 2" means 8 to 15 June.
In this data, every patient, doctor and department has at least one visit. I found out this makes some joins look the same, so in a few places I asked a narrower question (like "Emergency visits only") to show the difference.
The data has no empty (NULL) IDs.

Part 1: Window Functions
1.1 The problem window functions solve

I already knew GROUP BY from class. It combines many rows into one row per group:
SELECT doctor_id, SUM(total_bill_kes) AS doctor_total
FROM visits
WHERE doctor_id IN (103, 104)
GROUP BY doctor_id;
doctor_id | doctor_total
-----------+--------------
103 | 105200.00
104 | 74900.00
(2 rows)
That gave me the totals, but I lost the individual visits. I wanted to see each visit with the doctor's total beside it, and with GROUP BY alone I couldn't do that.
That's exactly what a window function does. It does the maths for a group, but it doesn't combine the rows. Every row remains.
SELECT visit_id, doctor_id, total_bill_kes,
SUM(total_bill_kes) OVER (PARTITION BY doctor_id) AS doctor_total
FROM visits
WHERE doctor_id IN (103, 104)
ORDER BY doctor_id, visit_id;
visit_id | doctor_id | total_bill_kes | doctor_total
----------+-----------+----------------+--------------
1003 | 103 | 28000.00 | 105200.00
1008 | 103 | 6500.00 | 105200.00
1015 | 103 | 24000.00 | 105200.00
1020 | 103 | 6500.00 | 105200.00
1023 | 103 | 7200.00 | 105200.00
1027 | 103 | 33000.00 | 105200.00
1004 | 104 | 42000.00 | 74900.00
1007 | 104 | 5500.00 | 74900.00
1014 | 104 | 1800.00 | 74900.00
1019 | 104 | 5200.00 | 74900.00
1024 | 104 | 18500.00 | 74900.00
1028 | 104 | 1900.00 | 74900.00
(12 rows)
The totals are the same as before, but all 12 visits are still there.
We learn that GROUP BY gives one row per group. A window function gives every row, plus something about its group.
1.2 How a window function is written: OVER, PARTITION BY, ORDER BY and window frames

I learnt that a window function always has the word OVER after it. The brackets describe the "window", which is just the set of rows the function looks at.
function_name() OVER (
PARTITION BY column -- split rows into groups (optional)
ORDER BY column -- sort rows inside each group (optional)
ROWS BETWEEN ... -- the frame: which nearby rows to use (optional)
)
Here's what I learnt about each part.
OVER()

Empty brackets mean "look at all the rows". I used this to put the hospital's grand total next to every visit and work out each visit's share of the money:
SELECT visit_id, total_bill_kes,
SUM(total_bill_kes) OVER () AS hospital_total,
ROUND(100.0 * total_bill_kes / SUM(total_bill_kes) OVER (), 1) AS percent_of_total
FROM visits
ORDER BY total_bill_kes DESC
LIMIT 5;
visit_id | total_bill_kes | hospital_total | percent_of_total
----------+----------------+----------------+------------------
1017 | 82000.00 | 488000.00 | 16.8
1010 | 76000.00 | 488000.00 | 15.6
1004 | 42000.00 | 488000.00 | 8.6
1016 | 36000.00 | 488000.00 | 7.4
1027 | 33000.00 | 488000.00 | 6.8
(5 rows)
The output told me that one visit (1017, a heart failure patient) brought in almost 17% of all the money in these two weeks. I wouldn't have spotted that from the raw table.
PARTITION BY

PARTITION BY splits the rows into groups, and the function starts again for each group. It's like GROUP BY, except the rows aren't combined. I used PARTITION BY doctor_id above to get each doctor's total.
ORDER BY (inside OVER)

This sorts the rows before the function does its work. It matters whenever order matters: numbering rows 1, 2, 3, looking at the previous row, or keeping a running total.
ORDER BY inside OVER (...) is not the same as the ORDER BY at the end of the query. The one inside changes how the answer is worked out. The one at the end only changes how the rows are shown.
Window frames

A frame says which rows near the current row to include. These are the ones I used:
FrameIn plain wordsROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWFrom the very first row up to this one (a running total)ROWS BETWEEN 2 PRECEDING AND CURRENT ROWThis row and the two before it (a 3-row average)ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGEvery row in the group

Something I noticed is that when I wrote a running total with ORDER BY but no frame, the numbers looked wrong. I found out that without a frame, PostgreSQL quietly uses RANGE instead of ROWS, and RANGE treats rows with the same value as one block. Our hospital has two visits every day, so this is what happened:
SELECT visit_id, visit_date, total_bill_kes,
SUM(total_bill_kes) OVER (ORDER BY visit_date) AS running_default,
SUM(total_bill_kes) OVER (ORDER BY visit_date, visit_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_rows
FROM visits
ORDER BY visit_date, visit_id
LIMIT 6;
visit_id | visit_date | total_bill_kes | running_default | running_rows
----------+------------+----------------+-----------------+--------------
1001 | 2026-06-01 | 3500.00 | 6000.00 | 3500.00
1002 | 2026-06-01 | 2500.00 | 6000.00 | 6000.00
1003 | 2026-06-02 | 28000.00 | 76000.00 | 34000.00
1004 | 2026-06-02 | 42000.00 | 76000.00 | 76000.00
1005 | 2026-06-03 | 4000.00 | 83000.00 | 80000.00
1006 | 2026-06-03 | 3000.00 | 83000.00 | 83000.00
(6 rows)

running_default adds both visits from the same day together. That's why both 1 June rows say 6,000.
running_rows adds one visit at a time: 3,500, then 6,000, then 34,000, and so on.
In the end I understood that neither one is wrong. The first answers "how much by the end of this day?" The second answers "how much up to this visit?"
1.3 Ranking functions: ROW_NUMBER, RANK, DENSE_RANK and NTILE
ROW_NUMBER()

This function numbers the rows 1, 2, 3... Every row gets its own number, even if two rows look the same.
It can be used to count things in order, like a patient's 1st visit, 2nd visit, and so on.
In my practice, I numbered each returning patient's visits by date.
SELECT patient_id, visit_id, visit_date,
ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY visit_date) AS visit_number
FROM visits
WHERE patient_id IN (1, 2, 5, 7, 10)
ORDER BY patient_id, visit_number;
patient_id | visit_id | visit_date | visit_number
------------+----------+------------+--------------
1 | 1001 | 2026-06-01 | 1
1 | 1011 | 2026-06-06 | 2
2 | 1002 | 2026-06-01 | 1
2 | 1021 | 2026-06-11 | 2
5 | 1005 | 2026-06-03 | 1
5 | 1025 | 2026-06-13 | 2
7 | 1007 | 2026-06-04 | 1
7 | 1028 | 2026-06-14 | 2
10 | 1010 | 2026-06-05 | 1
10 | 1030 | 2026-06-15 | 2
(10 rows)
The output showed that because of PARTITION BY patient_id, the count starts again at 1 for every patient. Any row marked 2 is a return visit.
RANK() and DENSE_RANK()

These functions give each row a position, like runners in a race. If two rows tie, they get the same position. The difference is what comes next:
RANK() skips a number after a tie: 1, 1, 3
DENSE_RANK() doesn't skip: 1, 1, 2
In my practice, I ranked the visits by patient satisfaction score, highest first. Some visits have the same score, so I put all three functions side by side to see how they differ.
SELECT visit_id, patient_satisfaction_score AS score,
ROW_NUMBER() OVER (ORDER BY patient_satisfaction_score DESC, visit_id) AS row_num,
RANK() OVER (ORDER BY patient_satisfaction_score DESC) AS rank,
DENSE_RANK() OVER (ORDER BY patient_satisfaction_score DESC) AS dense_rank
FROM visits
ORDER BY score DESC, visit_id
LIMIT 9;
visit_id | score | row_num | rank | dense_rank
----------+-------+---------+------+------------
1011 | 4.8 | 1 | 1 | 1
1028 | 4.8 | 2 | 1 | 1
1005 | 4.7 | 3 | 3 | 2
1019 | 4.7 | 4 | 3 | 2
1007 | 4.6 | 5 | 5 | 3
1015 | 4.6 | 6 | 5 | 3
1025 | 4.6 | 7 | 5 | 3
1001 | 4.5 | 8 | 8 | 4
1013 | 4.5 | 9 | 8 | 4
(9 rows)
In the output, visits 1011 and 1028 both scored 4.8, so RANK and DENSE_RANK both put them in 1st place.
The next score (4.7) is 3rd with RANK, because two visits already took 1st and 2nd. With DENSE_RANK it's 2nd.
ROW_NUMBER ignores ties completely and just counts 1 to 9.
When I first ran this, tied visits came out of ROW_NUMBER in a different order each time. I learnt that if rows tie, PostgreSQL can number them in any order. Adding visit_id to its sort made the result the same every time.
FunctionDo ties share a number?Does it skip after a tie?I'd use it when I want...ROW_NUMBER()No–exactly one row per numberRANK()YesYesrace-style positionsDENSE_RANK()YesNothe "top 3 scores", even if more than 3 rows have them
NTILE(n)

NTILE(n) splits the rows into n groups of about the same size and says which group each row is in.
It is used to sort things into bands, like cheap, middle and expensive.
In practice, I split the 9 inpatient visits into 3 cost groups.
SELECT visit_id, total_bill_kes,
NTILE(3) OVER (ORDER BY total_bill_kes) AS cost_group
FROM visits
WHERE admission_type = 'Inpatient'
ORDER BY total_bill_kes;
visit_id | total_bill_kes | cost_group
----------+----------------+------------
1024 | 18500.00 | 1
1015 | 24000.00 | 1
1003 | 28000.00 | 1
1026 | 29000.00 | 2
1027 | 33000.00 | 2
1016 | 36000.00 | 2
1004 | 42000.00 | 3
1010 | 76000.00 | 3
1017 | 82000.00 | 3
(9 rows)
As per the output, group 1 holds the three cheapest stays and group 3 the three most expensive. I also read that if the rows don't split evenly, the first groups just get one extra row each.
1.4 Looking at other rows: LAG, LEAD, FIRST_VALUE and LAST_VALUE
LAG() and LEAD()

LAG looks back at the previous row. LEAD looks ahead at the next row.
They are used to compare a row with the one before or after it. For example, "How many days since this patient was last here?"
In my practice, for each returning patient, I showed their previous visit, their next visit, and the number of days in between.
SELECT patient_id, visit_id, visit_date,
LAG(visit_date) OVER (PARTITION BY patient_id ORDER BY visit_date) AS previous_visit,
LEAD(visit_date) OVER (PARTITION BY patient_id ORDER BY visit_date) AS next_visit,
visit_date - LAG(visit_date) OVER (PARTITION BY patient_id ORDER BY visit_date) AS days_since_last
FROM visits
WHERE patient_id IN (1, 2, 5, 7, 10)
ORDER BY patient_id, visit_date;
patient_id | visit_id | visit_date | previous_visit | next_visit | days_since_last
------------+----------+------------+----------------+------------+-----------------
1 | 1001 | 2026-06-01 | NULL | 2026-06-06 | NULL
1 | 1011 | 2026-06-06 | 2026-06-01 | NULL | 5
2 | 1002 | 2026-06-01 | NULL | 2026-06-11 | NULL
2 | 1021 | 2026-06-11 | 2026-06-01 | NULL | 10
5 | 1005 | 2026-06-03 | NULL | 2026-06-13 | NULL
5 | 1025 | 2026-06-13 | 2026-06-03 | NULL | 10
7 | 1007 | 2026-06-04 | NULL | 2026-06-14 | NULL
7 | 1028 | 2026-06-14 | 2026-06-04 | NULL | 10
10 | 1010 | 2026-06-05 | NULL | 2026-06-15 | NULL
10 | 1030 | 2026-06-15 | 2026-06-05 | NULL | 10
(10 rows)
The output showed that patient 1 came back after 5 days, and everyone else came back after 10. A first visit has nothing before it, so LAG shows NULL. A last visit has nothing after it, so LEAD shows NULL.
FIRST_VALUE() and LAST_VALUE()

FIRST_VALUE takes the value from the first row in the window. LAST_VALUE takes it from the last row.
Using our hospital data, for each of Dr. Faith Chebet's visits (doctor 104), I showed the bill from her first visit and from her last visit.
SELECT visit_id, visit_date, total_bill_kes,
FIRST_VALUE(total_bill_kes) OVER (ORDER BY visit_date, visit_id) AS first_bill,
LAST_VALUE(total_bill_kes) OVER (ORDER BY visit_date, visit_id) AS last_bill_default,
LAST_VALUE(total_bill_kes) OVER (ORDER BY visit_date, visit_id
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS last_bill_full
FROM visits
WHERE doctor_id = 104
ORDER BY visit_date, visit_id;
visit_id | visit_date | total_bill_kes | first_bill | last_bill_default | last_bill_full
----------+------------+----------------+------------+-------------------+----------------
1004 | 2026-06-02 | 42000.00 | 42000.00 | 42000.00 | 1900.00
1007 | 2026-06-04 | 5500.00 | 42000.00 | 5500.00 | 1900.00
1014 | 2026-06-07 | 1800.00 | 42000.00 | 1800.00 | 1900.00
1019 | 2026-06-10 | 5200.00 | 42000.00 | 5200.00 | 1900.00
1024 | 2026-06-12 | 18500.00 | 42000.00 | 18500.00 | 1900.00
1028 | 2026-06-14 | 1900.00 | 42000.00 | 1900.00 | 1900.00
(6 rows)

The output showed that first_bill is 42,000 on every row. That's her first visit (1004), so that part worked straight away.
last_bill_default just copied each row's own bill. The reason is the same hidden default frame from earlier. It stops at the current row, so from each row's point of view, the "last" row is itself.
last_bill_full tells it to look at all the rows, and then it correctly shows 1,900, her last visit (1028).
Lesson learnt: whenever I use LAST_VALUE, I add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
1.5 Aggregate window functions: SUM, AVG, COUNT, MIN and MAX

I knew these five from the GROUP BY chapter of our notes. What I learnt is that putting OVER (...) after them turns them into window functions with the same answers, but every row stays.
I put each Orthopedics visit (department 30) next to its department's count, total, average, lowest and highest bill.
SELECT visit_id, total_bill_kes,
COUNT(*) OVER (PARTITION BY department_id) AS dept_visits,
SUM(total_bill_kes) OVER (PARTITION BY department_id) AS dept_total,
ROUND(AVG(total_bill_kes) OVER (PARTITION BY department_id), 2) AS dept_avg,
MIN(total_bill_kes) OVER (PARTITION BY department_id) AS dept_min,
MAX(total_bill_kes) OVER (PARTITION BY department_id) AS dept_max
FROM visits
WHERE department_id = 30
ORDER BY visit_id;
visit_id | total_bill_kes | dept_visits | dept_total | dept_avg | dept_min | dept_max
----------+----------------+-------------+------------+----------+----------+----------
1003 | 28000.00 | 6 | 105200.00 | 17533.33 | 6500.00 | 33000.00
1008 | 6500.00 | 6 | 105200.00 | 17533.33 | 6500.00 | 33000.00
1015 | 24000.00 | 6 | 105200.00 | 17533.33 | 6500.00 | 33000.00
1020 | 6500.00 | 6 | 105200.00 | 17533.33 | 6500.00 | 33000.00
1023 | 7200.00 | 6 | 105200.00 | 17533.33 | 6500.00 | 33000.00
1027 | 33000.00 | 6 | 105200.00 | 17533.33 | 6500.00 | 33000.00
(6 rows)
The results show the average Orthopedics bill is KES 17,533.33. With that number on every row, we see that visits 1003, 1015 and 1027 are above average and the other three are below.
A moving average

Adding ORDER BY and a frame turns AVG into a moving average, which is an average that slides along the rows. This one averages each visit with the two before it:
SELECT visit_id, visit_date, total_bill_kes,
ROUND(AVG(total_bill_kes) OVER (ORDER BY visit_date, visit_id
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3
FROM visits
WHERE doctor_id = 104
ORDER BY visit_date, visit_id;
visit_id | visit_date | total_bill_kes | moving_avg_3
----------+------------+----------------+--------------
1004 | 2026-06-02 | 42000.00 | 42000.00
1007 | 2026-06-04 | 5500.00 | 23750.00
1014 | 2026-06-07 | 1800.00 | 16433.33
1019 | 2026-06-10 | 5200.00 | 4166.67
1024 | 2026-06-12 | 18500.00 | 8500.00
1028 | 2026-06-14 | 1900.00 | 8533.33
(6 rows)
The output shows that the first row has nothing before it, so its average is just itself. The second row averages two visits: (42,000 + 5,500) ÷ 2 = 23,750. From the third row on, it always uses three. I could see how a moving average evens out a one-off big bill like that 42,000.
1.6 A mistake I made: putting a window function in WHERE

I wanted the most expensive visit in each department, so my first try was this:
SELECT visit_id, department_id, total_bill_kes,
RANK() OVER (PARTITION BY department_id ORDER BY total_bill_kes DESC) AS bill_rank
FROM visits
WHERE RANK() OVER (PARTITION BY department_id ORDER BY total_bill_kes DESC) = 1;
PostgreSQL gave me an error:
ERROR: window functions are not allowed in WHERE
Why it failed: this is where the "order of execution" chapter in our notes finally clicked for me. PostgreSQL runs a query in this order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Window functions are worked out in the SELECT step, which comes after WHERE. So when WHERE runs, the rank doesn't exist yet.
How I fixed it: the trick is to do it in two steps. First I worked out the rank inside a CTE (I explain CTEs in Part 3), and then I filtered on that rank in the main query. By the time the main query's WHERE runs, the rank already exists, so it works:
WITH ranked AS (
SELECT visit_id, department_id, total_bill_kes,
RANK() OVER (PARTITION BY department_id ORDER BY total_bill_kes DESC) AS bill_rank
FROM visits
)
SELECT visit_id, department_id, total_bill_kes
FROM ranked
WHERE bill_rank = 1
ORDER BY department_id;
visit_id | department_id | total_bill_kes
----------+---------------+----------------
1017 | 10 | 82000.00
1016 | 20 | 36000.00
1027 | 30 | 33000.00
1004 | 40 | 42000.00
(4 rows)
Now I get the most expensive visit in each of the four departments, which is what I wanted in the first place.

Part 2: Joins

As I mentioned earlier, the visits table only has numbers like patient_id = 3 and doctor_id = 103. The names live in the patients and doctors tables. I learnt that storing each thing only once is good, but it means that to make a report people can read, I have to stitch the tables back together. That's what a join does.
Every join needs an ON part that says how the rows match up. Usually it's "this ID equals that ID".
2.1 INNER JOIN

INNER JOIN keeps only the rows that have matching values in both tables. We use it when we only care about rows that exist on both sides.
I showed each visit with the patient's name and the doctor's name.
SELECT v.visit_id, p.patient_name, d.doctor_name, v.total_bill_kes
FROM visits AS v
INNER JOIN patients AS p ON v.patient_id = p.patient_id
INNER JOIN doctors AS d ON v.doctor_id = d.doctor_id
ORDER BY v.visit_id
LIMIT 5;
visit_id | patient_name | doctor_name | total_bill_kes
----------+----------------+-------------------+----------------
1001 | Amina Otieno | DR.Brian Mwangi | 3500.00
1002 | John Kariuki | Dr. Sarah Wanjiku | 2500.00
1003 | Grace Naliaka | Dr. Peter Otieno | 28000.00
1004 | David Mutua | Dr. Faith Chebet | 42000.00
1005 | Esther Achieng | DR.Brian Mwangi | 4000.00
(5 rows)
v, p and d are nicknames for the tables, so I don't have to type the full names every time. v.patient_id just means "the patient_id column from visits". I also learnt you can join as many tables as you need, one JOIN at a time.
Joins work with GROUP BY too. Here's how much each department billed, using the real department names:
SELECT dep.department_name, COUNT(*) AS visits, SUM(v.total_bill_kes) AS total_billed
FROM visits AS v
INNER JOIN department AS dep ON v.department_id = dep.department_id
GROUP BY dep.department_name
ORDER BY total_billed DESC;
department_name | visits | total_billed
------------------+--------+--------------
Cardiology | 9 | 210100.00
Orthopedics | 6 | 105200.00
General Medicine | 9 | 97800.00
Pediatrics | 6 | 74900.00
(4 rows)
The output shows that Cardiology and General Medicine each saw 9 visits, but Cardiology made more than twice as much money.
2.2 LEFT JOIN

The LEFT JOIN keeps every row from the left table (the one after FROM), and adds matching rows from the right table. Where there's no match, the right side is NULL (empty).
We use it when we want to keep everything from one table, even rows with no match. For example, "all doctors, even the ones who had no emergency cases".
At first I tried a plain LEFT JOIN from doctors to visits, and I got the same 30 rows as an INNER JOIN. That confused me until I realised every doctor in our data has visits, so there was nothing unmatched to show. The difference only appears when something has no match. So I decided to ask: which doctors handled an Emergency visit?
SELECT d.doctor_name, v.visit_id, v.admission_type
FROM doctors AS d
LEFT JOIN visits AS v
ON d.doctor_id = v.doctor_id
AND v.admission_type = 'Emergency'
ORDER BY d.doctor_id;
doctor_name | visit_id | admission_type
-------------------+----------+----------------
DR.Brian Mwangi | 1030 | Emergency
Dr. Sarah Wanjiku | NULL | NULL
Dr. Peter Otieno | NULL | NULL
Dr. Faith Chebet | NULL | NULL
(4 rows)
The result shows all four doctors are listed, but only Dr. Brian Mwangi had an emergency. The NULLs for the other three just mean "no emergency visit found".
A mistake I learnt from: filtering a LEFT JOIN in WHERE

Before I got the query above right, I had put the Emergency check in WHERE instead of in ON, like this:
SELECT d.doctor_name, v.visit_id, v.admission_type
FROM doctors AS d
LEFT JOIN visits AS v ON d.doctor_id = v.doctor_id
WHERE v.admission_type = 'Emergency'
ORDER BY d.doctor_id;
doctor_name | visit_id | admission_type
-----------------+----------+----------------
DR.Brian Mwangi | 1030 | Emergency
(1 row)
Three of the doctors disappeared. It took me a while to see why, and again the answer was the order SQL runs in. WHERE runs after the join, so it looks at every row and throws out anything that isn't 'Emergency'. That includes the empty rows the LEFT JOIN was keeping for the other three doctors. So without meaning to, I had turned my LEFT JOIN into an INNER JOIN.
What I took away from this: with a LEFT JOIN, if I'm filtering the right-hand table and still want to keep the rows with no match, the condition goes in ON, not WHERE.
2.3 RIGHT JOIN

The RIGHT JOIN mirrors the LEFT JOIN. It keeps every row from the right table (the one after JOIN), and adds matching rows from the left table. It is used when we want to keep every row from one table, even rows with no match. The only difference is which side that table sits on.
To really see this, I asked the exact same question as in my LEFT JOIN example: which doctors handled an Emergency visit?
This time I started FROM visits and put doctors on the right, after RIGHT JOIN. Because doctors is now the right-hand table, RIGHT JOIN keeps every doctor:
SELECT d.doctor_name, v.visit_id, v.admission_type
FROM visits AS v
RIGHT JOIN doctors AS d
ON v.doctor_id = d.doctor_id
AND v.admission_type = 'Emergency'
ORDER BY d.doctor_id;
doctor_name | visit_id | admission_type
-------------------+----------+----------------
DR.Brian Mwangi | 1030 | Emergency
Dr. Sarah Wanjiku | NULL | NULL
Dr. Peter Otieno | NULL | NULL
Dr. Faith Chebet | NULL | NULL
(4 rows)
The output is exactly the same as the LEFT JOIN. A LEFT JOIN B is the same as B RIGHT JOIN A. From my reading, most people stick to LEFT JOIN because "start with this table, then add that one" is easier to follow. RIGHT JOIN is useful when adding one more table to the end of a long query and you want to keep all of its rows.
2.4 FULL OUTER JOIN

FULL OUTER JOIN keeps every row from both tables. Rows that match are joined. Rows that don't match get NULL on the side that's missing.
We use it to compare two lists and see what's in one, what's in the other, and what's in both.
I compared the patients who came in Week 1 (1–7 June) with those who came in Week 2 (8–15 June). Each list is a small query in brackets, called a subquery, which I explain in Part 4.
SELECT w1.patient_id AS week1_patient,
w2.patient_id AS week2_patient
FROM (SELECT DISTINCT patient_id FROM visits
WHERE visit_date BETWEEN '2026-06-01' AND '2026-06-07') AS w1
FULL OUTER JOIN
(SELECT DISTINCT patient_id FROM visits
WHERE visit_date BETWEEN '2026-06-08' AND '2026-06-15') AS w2
ON w1.patient_id = w2.patient_id
ORDER BY COALESCE(w1.patient_id, w2.patient_id);
week1_patient | week2_patient
---------------+---------------
1 | NULL
2 | 2
3 | NULL
4 | NULL
5 | 5
6 | NULL
7 | 7
8 | NULL
9 | NULL
10 | 10
11 | NULL
12 | NULL
13 | NULL
NULL | 14
NULL | 15
NULL | 16
NULL | 17
NULL | 18
NULL | 19
NULL | 20
NULL | 21
NULL | 22
NULL | 23
NULL | 24
NULL | 25
(25 rows)
From the output:
4 patients came in both weeks (2, 5, 7 and 10), so both columns are filled.
9 patients came only in Week 1. Patient 1 came twice, but both visits were in Week 1.
12 patients came only in Week 2.
An INNER JOIN would only show the 4 who came both weeks. A LEFT JOIN would only show the 13 from Week 1. The FULL OUTER JOIN shows all 25.
(COALESCE(a, b) means "use a, but if it's empty, use b". I used it from Chapter 19 of our notes to keep the list sorted by patient number.)
2.5 CROSS JOIN

CROSS JOIN pairs every row from one table with every row from the other. There's no ON. 4 rows × 4 rows = 16 rows.
We use it when we want every possible pair, like a blank duty rota listing every doctor against every ward.
SELECT d.doctor_name, w.ward_name
FROM doctors AS d
CROSS JOIN wards AS w
ORDER BY d.doctor_id, w.ward_id
LIMIT 6;
doctor_name | ward_name
-------------------+-------------------
DR.Brian Mwangi | Cardiac Care Unit
DR.Brian Mwangi | General Ward
DR.Brian Mwangi | Surgical Ward
DR.Brian Mwangi | Childrens Ward
Dr. Sarah Wanjiku | Cardiac Care Unit
Dr. Sarah Wanjiku | General Ward
(6 rows)
Without the LIMIT there are 16 rows: 4 doctors × 4 wards.
2.6 SELF JOIN

A SELF JOIN joins a table to itself. I was surprised there's no special keyword for it. You just use the same table twice with two different nicknames. It's used to compare rows in a table with other rows in the same table.
We try to answer the question: which patients come from the same county?
SELECT p1.patient_name AS patient_1,
p2.patient_name AS patient_2,
p1.county
FROM patients AS p1
INNER JOIN patients AS p2
ON p1.county = p2.county
AND p1.patient_id < p2.patient_id
ORDER BY p1.county;
patient_1 | patient_2 | county
----------------+--------------+---------
Esther Achieng | Brian Omondi | Kisumu
Amina Otieno | Isaac Kamau | Nairobi
(2 rows)
The p1.patient_id < p2.patient_id part took me a moment to understand. Without it, every patient gets matched with themselves, and every pair shows up twice (Esther–Brian and Brian–Esther).
I also tried to match each returning patient's first visit with their later one.
SELECT a.patient_id, a.visit_id AS first_visit, a.visit_date AS first_date,
b.visit_id AS later_visit, b.visit_date AS later_date
FROM visits AS a
INNER JOIN visits AS b
ON a.patient_id = b.patient_id
AND a.visit_date < b.visit_date
ORDER BY a.patient_id;
patient_id | first_visit | first_date | later_visit | later_date
------------+-------------+------------+-------------+------------
1 | 1001 | 2026-06-01 | 1011 | 2026-06-06
2 | 1002 | 2026-06-01 | 1021 | 2026-06-11
5 | 1005 | 2026-06-03 | 1025 | 2026-06-13
7 | 1007 | 2026-06-04 | 1028 | 2026-06-14
10 | 1010 | 2026-06-05 | 1030 | 2026-06-15
(5 rows)
From my research, self joins are also how you'd show "who is whose boss" if a table had a column like supervisor_id. Our doctors table doesn't have one, so I couldn't show that here.
2.7 How the joins compare

After trying all six, it helped me to put them next to each other. The main difference between them is simply which rows they keep when there's no match:
JoinRows kept from the left tableRows kept from the right tableWhen I'd use itMy exampleINNER JOINonly matchesonly matchesI only want rows that exist on both sidesVisits with patient and doctor namesLEFT JOINallonly matchesI want everything from the first table, matched or notAll doctors, with any emergency visitsRIGHT JOINonly matchesallSame as LEFT, but the table I want to keep is on the rightSame question, tables swappedFULL OUTER JOINallallI want to compare two lists and see what's missing on either sideWeek 1 patients vs Week 2 patientsCROSS JOINevery pairevery pairI want every possible combinationEvery doctor with every wardSELF JOINthe table joined to itself–I want to compare rows within one tablePatients from the same county
Part 3: CTEs (Common Table Expressions)

Our notes (Chapter 14) introduced CTEs briefly. A CTE lets you give a name to a query and use that name like a table inside a bigger query. It starts with the word WITH, and it only lasts while that one query runs.
WITH name_of_cte AS (
SELECT ...
)
SELECT ... FROM name_of_cte;
Why it helps: my queries were getting long and hard to follow. A CTE breaks one big query into small, named steps that read from top to bottom.
3.1 A basic CTE

A basic CTE using the question: which departments billed more than KES 100,000?
WITH department_totals AS (
SELECT department_id, SUM(total_bill_kes) AS total_billed
FROM visits
GROUP BY department_id
)
SELECT department_id, total_billed
FROM department_totals
WHERE total_billed > 100000
ORDER BY total_billed DESC;
department_id | total_billed
---------------+--------------
10 | 210100.00
30 | 105200.00
(2 rows)
The result shows that Cardiology (10) and Orthopedics (30) each billed more than KES 100,000.
The CTE department_totals adds up the bills for each department. That gives four rows.
The main query reads from department_totals like any other table and keeps only the totals over 100,000.

3.2 Multiple CTEs

I learnt you can write several CTEs in one WITH, with commas between them, and a later CTE can use an earlier one. For example: which doctors billed more than the average doctor?
WITH doctor_totals AS (
SELECT doctor_id, SUM(total_bill_kes) AS total_billed
FROM visits
GROUP BY doctor_id
),
average_total AS (
SELECT AVG(total_billed) AS avg_billed
FROM doctor_totals
)
SELECT dt.doctor_id, dt.total_billed, ROUND(a.avg_billed, 2) AS avg_billed
FROM doctor_totals AS dt
CROSS JOIN average_total AS a
WHERE dt.total_billed > a.avg_billed;
doctor_id | total_billed | avg_billed
-----------+--------------+------------
101 | 210100.00 | 122000.00
(1 row)

We see that doctor_totals works out each doctor's total, giving 4 rows.
average_total uses those 4 totals to get the average, KES 122,000. That's just 1 row.
The main query puts them together. A CROSS JOIN works here because average_total has only one row, so it simply adds the average to every doctor's row. Then it keeps only the doctors above it.
The output shows that only Dr. Brian Mwangi (101) billed more than the average doctor.
3.3 CTEs with joins and aggregations

We use the example: who are the top five patients by money spent, and where are they from?
WITH patient_spend AS (
SELECT patient_id,
COUNT(*) AS visits,
SUM(total_bill_kes) AS total_spent
FROM visits
GROUP BY patient_id
)
SELECT p.patient_name, p.county, ps.visits, ps.total_spent
FROM patient_spend AS ps
INNER JOIN patients AS p ON ps.patient_id = p.patient_id
ORDER BY ps.total_spent DESC
LIMIT 5;
patient_name | county | visits | total_spent
--------------------+-------------+--------+-------------
Peter Ouma | Siaya | 2 | 98000.00
Hellen Jepchirchir | Bomet | 1 | 82000.00
David Mutua | Machakos | 1 | 42000.00
Francis Njuguna | Laikipia | 1 | 36000.00
Ruth Nekesa | Trans Nzoia | 1 | 33000.00
(5 rows)
We see that the CTE does the adding up (the aggregation), and the main query adds the names (the join). Peter Ouma spent the most, KES 98,000 over two visits: a heart failure stay, and later an emergency visit for chest pain.
3.4 Recursive CTEs

A recursive CTE is one that uses itself. It builds a list one step at a time, a bit like counting on your fingers. It has two parts, joined by UNION ALL: a starting point, and a next step that takes the last row and makes the next one. It keeps going until a condition tells it to stop.
I started with the simplest example I could think of, counting from 1 to 5:
WITH RECURSIVE counter AS (
SELECT 1 AS n -- start at 1
UNION ALL
SELECT n + 1 FROM counter WHERE n < 5 -- add 1 until we reach 5
)
SELECT n FROM counter;
n
---
1
2
3
4
5
(5 rows)
Using the hospital data, I built a calendar for Week 1 and counted the visits on each day. The reason for a calendar is that a normal GROUP BY visit_date can only show days that had visits. A calendar lists every day, so a quiet day would show 0 instead of just being missing.
WITH RECURSIVE calendar AS (
SELECT DATE '2026-06-01' AS day
UNION ALL
SELECT (day + 1)::DATE FROM calendar WHERE day < DATE '2026-06-07'
)
SELECT c.day,
COUNT(v.visit_id) AS visits,
COALESCE(SUM(v.total_bill_kes), 0) AS billed
FROM calendar AS c
LEFT JOIN visits AS v ON v.visit_date = c.day
GROUP BY c.day
ORDER BY c.day;
day | visits | billed
------------+--------+----------
2026-06-01 | 2 | 6000.00
2026-06-02 | 2 | 70000.00
2026-06-03 | 2 | 7000.00
2026-06-04 | 2 | 12000.00
2026-06-05 | 2 | 91000.00
2026-06-06 | 2 | 6500.00
2026-06-07 | 2 | 8800.00
(7 rows)
The output shows that the hospital had exactly 2 visits every day, and 5 June brought in the most money (KES 91,000), mostly from one heart failure patient. I was pleased that this one query uses three things I learnt in this assignment: a recursive CTE, a LEFT JOIN and a total.
From my research, recursive CTEs are also the usual way to follow a chain, like "this doctor reports to that doctor, who reports to..." Our data doesn't have that kind of column, so I used a calendar instead.
3.5 CTEs vs subqueries

A subquery (Part 4) is a query written inside another query. While I was learning about CTEs, I kept wondering how they were different from subqueries. So I took my first CTE from 3.1 and rewrote it as a subquery to compare them side by side:
SELECT department_id, total_billed
FROM (SELECT department_id, SUM(total_bill_kes) AS total_billed
FROM visits
GROUP BY department_id) AS department_totals
WHERE total_billed > 100000
ORDER BY total_billed DESC;
department_id | total_billed
---------------+--------------
10 | 210100.00
30 | 105200.00
(2 rows)
It gave exactly the same answer. The difference isn't in the result. It's in how the query is written and how easy it is to read. With the CTE, I name the step first and then use it, so it reads from top to bottom. With the subquery, the step is tucked inside the main query, so I have to read from the inside out.
This is how I'd sum up the differences I found:
CTESubqueryWhere you write itAt the top, before the main queryInside the main queryDoes it have a name?YesOnly when it's in FROMCan you use it twice in the same query?Yes, just use the name againNo, you'd have to type it out againCan it repeat itself (recursive)?YesNoEasy to read with many steps?Yes, top to bottomHarder, with brackets inside bracketsBest forStep-by-step logic and reusing resultsShort, one-off checks

I also read that in PostgreSQL 12 and newer, a simple CTE and the same subquery run about as fast as each other. So my takeaway is to pick whichever one is easier to read.

Part 4: SQL Subqueries

A subquery is a SELECT inside another query, wrapped in brackets. PostgreSQL answers the small question first, then uses that answer in the big one. You can put a subquery in WHERE, SELECT or FROM.
4.1 Scalar (single-value) subqueries

This is a subquery that gives back just one value: one row, one column. "Scalar" turned out to be just the fancy name for that. It can be used to compare against a number I'd otherwise have to look up first, like an average.
Scalar subquery in WHERE

For example, to answer the question: which visits cost more than the average visit?
First I checked the average on its own:
SELECT AVG(total_bill_kes) FROM visits;
avg
--------------------
16266.666666666667
(1 row)
Instead of copying that number into another query by hand, I let a subquery work it out:
SELECT visit_id, patient_id, total_bill_kes
FROM visits
WHERE total_bill_kes > (SELECT AVG(total_bill_kes) FROM visits)
ORDER BY total_bill_kes DESC;
visit_id | patient_id | total_bill_kes
----------+------------+----------------
1017 | 16 | 82000.00
1010 | 10 | 76000.00
1004 | 4 | 42000.00
1016 | 15 | 36000.00
1027 | 24 | 33000.00
1026 | 23 | 29000.00
1003 | 3 | 28000.00
1015 | 14 | 24000.00
1030 | 10 | 22000.00
1024 | 22 | 18500.00
(10 rows)
The output shows that 10 of the 30 visits cost more than the average (KES 16,266.67). I also realised that if new visits are added, the query still works, because it works out the average fresh every time.
Scalar subquery in SELECT

To answer the question: what share of the hospital's money did each department bring in?
SELECT department_id,
SUM(total_bill_kes) AS department_total,
ROUND(100.0 * SUM(total_bill_kes) / (SELECT SUM(total_bill_kes) FROM visits), 1) AS percent_of_hospital
FROM visits
GROUP BY department_id
ORDER BY department_id;
department_id | department_total | percent_of_hospital
---------------+------------------+---------------------
10 | 210100.00 | 43.1
20 | 97800.00 | 20.0
30 | 105200.00 | 21.6
40 | 74900.00 | 15.3
(4 rows)
The output shows that Cardiology (10) brought in 43% of all the money. The subquery gives the hospital total (488,000), and each department's total is divided by it.
4.2 Non-correlated vs correlated subqueries

This was the difference that took me longest to get:
A non-correlated subquery stands alone. You could copy it out and run it by itself, and PostgreSQL only runs it once. Both examples above are like this.
A correlated subquery borrows a value from the outer query, so it can't run by itself. It's worked out again for each row.
For example (correlated), to answer the question: which visits cost more than the average for their own department? I thought it was fairer to compare a Cardiology visit with other Cardiology visits, not with the whole hospital.
SELECT v.visit_id, v.department_id, v.total_bill_kes
FROM visits AS v
WHERE v.total_bill_kes > (SELECT AVG(v2.total_bill_kes)
FROM visits AS v2
WHERE v2.department_id = v.department_id)
ORDER BY v.department_id, v.total_bill_kes DESC;
visit_id | department_id | total_bill_kes
----------+---------------+----------------
1017 | 10 | 82000.00
1010 | 10 | 76000.00
1016 | 20 | 36000.00
1026 | 20 | 29000.00
1009 | 20 | 15000.00
1027 | 30 | 33000.00
1003 | 30 | 28000.00
1015 | 30 | 24000.00
1004 | 40 | 42000.00
1024 | 40 | 18500.00
(10 rows)
For reference, the department averages are: Cardiology 23,344.44, General Medicine 10,866.67, Orthopedics 17,533.33 and Pediatrics 12,483.33.
The output shows that visit 1009 (General Medicine, KES 15,000) is below the hospital average, so my earlier query skipped it. But it's above the General Medicine average, so this query includes it. The part that makes it correlated is WHERE v2.department_id = v.department_id: the inside query borrows v from the outside query.
Non-correlatedCorrelatedBorrows from the outer query?NoYesCan you run it by itself?YesNoHow often does it run?OnceOnce per outer rowMy exampleAbove the hospital averageAbove their department's average
4.3 Nested subqueries

A nested subquery is a subquery inside another subquery. PostgreSQL starts with the innermost one and works its way out.
My example: which patients saw the doctor who billed the most?
SELECT patient_name
FROM patients
WHERE patient_id IN (
SELECT patient_id
FROM visits
WHERE doctor_id = (
SELECT doctor_id
FROM visits
GROUP BY doctor_id
ORDER BY SUM(total_bill_kes) DESC
LIMIT 1))
ORDER BY patient_name;
patient_name
--------------------
Amina Otieno
Catherine Musyoka
Esther Achieng
Hellen Jepchirchir
John Kariuki
Peter Ouma
(6 rows)
How I read it, from the inside out:
The innermost query finds the doctor who billed the most: 101, Dr. Brian Mwangi (KES 210,100).
The middle query finds the patients he saw: 1, 2, 5, 10, 12 and 16.
The outer query swaps those numbers for names.

4.4 Subqueries with IN

IN (subquery) keeps a row if its value is on the list the subquery gives back.
My example: which visits were for a chronic (long-term) disease?
SELECT visit_id, patient_id, diagnosis_id
FROM visits
WHERE diagnosis_id IN (SELECT diagnosis_id
FROM diagnosis
WHERE diagnosis_category = 'Chronic Disease')
ORDER BY visit_id;
visit_id | patient_id | diagnosis_id
----------+------------+--------------
1001 | 1 | 501
1005 | 5 | 505
1010 | 10 | 510
1011 | 1 | 505
1013 | 12 | 501
1017 | 16 | 510
1021 | 2 | 501
1025 | 5 | 501
(8 rows)
Findings: the subquery gives back three chronic diseases (501 Hypertension, 505 Diabetes and 510 Heart Failure), and 8 visits were for one of them.
A trap I read about: NOT IN with an empty value

NOT IN keeps rows that are not on the list. But I learnt that if the list has even one NULL in it, NOT IN gives back nothing at all. Our data has no NULL IDs, so I tested it with a tiny list typed by hand:
SELECT COUNT(*) AS patients_found FROM patients WHERE patient_id NOT IN (1, 2);
patients_found
----------------
23
(1 row)
SELECT COUNT(*) AS patients_found FROM patients WHERE patient_id NOT IN (1, 2, NULL);
patients_found
----------------
0
(1 row)
Our notes taught us that NULL means "we don't know". PostgreSQL can't be sure patient 3 isn't the unknown value, so it plays it safe and keeps nothing. If a list might contain a NULL, NOT EXISTS (next) is the safer choice.
4.5 Subqueries with EXISTS

EXISTS asks a yes-or-no question: "Is there at least one matching row?" It doesn't care what's in the row, only whether there is one. That's why people usually write SELECT 1 inside it.
For example, which doctors had at least one emergency visit, and which had none?
SELECT d.doctor_name
FROM doctors AS d
WHERE EXISTS (SELECT 1
FROM visits AS v
WHERE v.doctor_id = d.doctor_id
AND v.admission_type = 'Emergency');
doctor_name
-----------------
DR.Brian Mwangi
(1 row)
SELECT d.doctor_name
FROM doctors AS d
WHERE NOT EXISTS (SELECT 1
FROM visits AS v
WHERE v.doctor_id = d.doctor_id
AND v.admission_type = 'Emergency');
doctor_name
-------------------
Dr. Sarah Wanjiku
Dr. Peter Otieno
Dr. Faith Chebet
(3 rows)
The output gives the same answer as my LEFT JOIN example in Part 2, just asked as a yes-or-no question for each doctor. I also noticed the inside query borrows d.doctor_id from the outside, so EXISTS subqueries are nearly always correlated.
INEXISTSThe question it asks"Is this value on the list?""Is there at least one matching row?"What the subquery gives backA list of valuesRows (only whether there are any matters)Safe with NULL when you use NOT?No, NOT IN breaksYes, NOT EXISTS is safe
4.6 Subqueries with ANY and ALL

ANY and ALL compare one value with a whole list, using >, <, = and so on. This is how I made sense of them:
> ANY (list) means "bigger than at least one of them", so bigger than the smallest.
> ALL (list) means "bigger than every one of them", so bigger than the biggest.
My example: I compared Cardiology visits (department 10) with Orthopedics bills (department 30). The six Orthopedics bills are 6,500, 6,500, 7,200, 24,000, 28,000 and 33,000.
ANY: which Cardiology visits cost more than at least one Orthopedics visit?
SELECT visit_id, total_bill_kes
FROM visits
WHERE department_id = 10
AND total_bill_kes > ANY (SELECT total_bill_kes
FROM visits
WHERE department_id = 30)
ORDER BY total_bill_kes;
visit_id | total_bill_kes
----------+----------------
1013 | 7000.00
1025 | 7600.00
1030 | 22000.00
1010 | 76000.00
1017 | 82000.00
(5 rows)
ALL: which Cardiology visits cost more than every Orthopedics visit?
SELECT visit_id, total_bill_kes
FROM visits
WHERE department_id = 10
AND total_bill_kes > ALL (SELECT total_bill_kes
FROM visits
WHERE department_id = 30)
ORDER BY total_bill_kes;
visit_id | total_bill_kes
----------+----------------
1010 | 76000.00
1017 | 82000.00
(2 rows)

> ANY kept the 5 Cardiology visits over 6,500, the cheapest Orthopedics bill.
> ALL kept only the 2 over 33,000, the most expensive Orthopedics bill.
I also found out that = ANY (...) means exactly the same as IN (...).

4.7 Subqueries in FROM

A subquery in FROM acts like a temporary table. I learnt that it must have a nickname. Here I called it totals.
For example, what's the average amount billed per doctor? That takes two steps: first each doctor's total, then the average of those totals.
SELECT ROUND(AVG(doctor_total), 2) AS avg_per_doctor
FROM (SELECT doctor_id, SUM(total_bill_kes) AS doctor_total
FROM visits
GROUP BY doctor_id) AS totals;
avg_per_doctor
----------------
122000.00
(1 row)
The output shows that the four doctor totals are 210,100, 97,800, 105,200 and 74,900, and their average is KES 122,000. That's the same number I got with two CTEs in Part 3, which was a nice way to check my work. You can't write AVG(SUM(...)) in one go, so the subquery handles the first step.
4.8 Where a subquery can go: SELECT, FROM and WHERE
WhereWhat it needs to give backMy exampleWHEREOne value (for >, =...), a list (for IN, ANY, ALL), or rows (for EXISTS)Visits above the average billSELECTOne value for each rowEach department's share of the totalFROMA table of rows, with a nicknameThe average of the doctor totals
What I learnt overall
TopicIn one lineA question it helped me answerWindow functionsDo maths across a group without losing any rows"Rank visits by satisfaction", "days since the last visit"JoinsCombine tables using matching IDs"Show each visit with the patient's and doctor's names"CTEsName the steps of a query so it reads top to bottom"Which doctors billed more than the average doctor?"SubqueriesA small question inside a bigger one"Which visits cost more than their department's average?"
#sql #postgres #beginners #database #software #coding #development #engineering #inclusive #community
Introduction to Databases and SQL with PostgreSQL: Window Functions, Joins, CTEs and Subqueries on a simple Hospital Database.
#beginners #sql #postgres #inclusive #engineering #development
argoul @[email protected]
2026-10-17

Police Python 357 d’Alain Corneau

Plongée dans le début des années 1970 avec ce film noir. Nous sommes en province catholique, à Orléans où les rues sont désertes à la nuit tombée, chacun chez soi, les secrets d’alcôve et de famille bien cachés. Le jour est tout d’apparences, la respectabilité, le métier, l’épouse et les enfants – que l’on conduit au catéchisme. Les églises sont ouvertes à tous les vents, jour et nuit. Ce qui va enclencher l’engrenage de cette histoire.
L’inspecteur Marc Ferrot (Yves Montand) traque les pilleurs d’église depuis un certain temps. Il en serre un qui a descellé une statue de la Vierge à l’Enfant et l’emporte comme butin. Son acolyte est bâillonné et ligoté dans la voiture. Alors que le voleur fuit, l’inspecteur effectue une sommation : arrêtez ou je tire. Il est en position adéquate, son revolver Python 357 bien dans l’axe des bras. L’autre stoppe, il le menotte. Mais Sylvia Leopardi (Stefania Sandrelli), une fille qui traînait par là – à minuit – est éblouie. Elle a pris la photo du mâle viril prolongé par son flingue. Elle l’agrandira pour la mettre dans la vitrine du fourreur où elle travaille, sous les arcades, pour attirer les femelles confites en train-train bourgeois.
Ils lient connaissance, ils se retrouvent parce que Ferrot a vu son portrait en action dans la vitrine. Et parce que chacun a eu un flash sur l’autre. Il n’y aura pas de sexe entre eux dans le film, juste une suggestion. Mais l’inspecteur, orphelin placé en foyer, est surtout en mal d’affection ; il a trouvé un jeune animal à caresser, à regarder marcher. Elle a un accent sexy et un nom italien. Elle joue avec lui comme une gamine immature auprès d’un grand frère. Elle pique son arme et tire un faisan dans un champ, au grand dam d’un chasseur présent. Elle joue à lui donner rendez-vous à la gare, au train en provenance de Paris, alors qu’elle habite Orléans, pour faire croire. Elle se fait déposer dans le centre pour ne pas qu’il voie où elle habite. Elle lui donne une montre que son amant lui a donnée, et qui appartenait à sa femme.
Ces jeux sont autant de cailloux blancs pour remonter le fil s’il y a enquête. Le chasseur a noté une 404 grise ; le contrôleur des billets à la sortie la remarque, ainsi que son mec ; le noctambule qui promène sa chienne lévrier ridicule à minuit (l’heure du crime) le débusque espionnant Sylvia, planqué derrière un arbre où pissait la bête qui piaille d’être dérangée ; la mémère nourrisseuse de chats ramasse un gant que le malotrus a fait tomber.
Or il y a enquête. La fille est en effet découverte massacrée dans son appartement, le crâne défoncé par un objet contondant (qu’on ne retrouve pas). Qui l’a fait ? Un amant ? L’« autre » qui serait jaloux ? Le commissaire Ganay (François Périer) se fait tenir au courant. Le spectateur le sait, c’est lui qui est le premier amant, le jaloux qui n’a pas supporté qu’elle en voie un autre, lui qui est l’assassin. La quête va donc porter non sur le coupable, ni sur le mobile, mais sur l’innocent. Comment va-t-il s’en sortir, ce solitaire un peu marginal dans cette ville de bourgeois, où les témoins hommes ou femmes sont des commères plus ou moins fiables qui se multiplient, prêtes à affirmer n’importe quoi pour se faire mousser.
Le jeune inspecteur Ménard (Mathieu Carrière), catholique qui ne badine pas avec la morale, est enthousiaste et s’étonne que son chef Ferrot élude les interrogatoires des témoins et ne veuille pas visiter l’appartement du crime. Le commissaire Ganay a pour épouse une infirme qui a de la fortune, issue d’une grande famille du coin avec gentilhommière, Mercedes et personnel de maison. Elle dépend de lui, elle accepte qu’il aille voir ailleurs pour ses besoins sexuels. Mais la jalousie de Ganay fait basculer cet équilibre. Il a tué celle qu’il entretenait parce qu’il la vue en bas de l’appartement où il l’attendait parler avec un homme, sans le reconnaître. Il laisse accuser un autre par souci de notable. En effet, Ferrot a pénétré chez Sylvia dans les minutes qui ont suivi le crime ; il a failli croiser son chef qui, voyant l’ascenseur monter, est descendu par l’escalier. On entend ses pas décroître. Il a laissé la porte non verrouillée parce que la poignée est cassée, il a effacé ses traces et emporté l’instrument du crime, ainsi que les bijoux et lettres qu’il a donné au fil des mois. Ferrot a laissé des traces de son passage, pas Ganay, qui voit au contraire la lumière s’allumer dans l’appartement du cinquième alors qu’il quitte le HLM. Son épouse, qui ne veut pas le perdre, le convainc de ne pas prendre les devants en se dénonçant, mais d’attendre et de voir.
Le lendemain, l’enquête commence, mais ni Ganay ni Ferrot ne sont pressés de la voir aboutir. Ganay est inquiet, mais Ferrot en détresse ; il aimait, il est à nouveau orphelin. Même si la fille avait un dossier à Paris qui révèle qu’elle a été chassée de chez elle en Italie par ses parents notables parce qu’elle avait mis le feu à la maison de son oncle, qui l’avait peut-être violée. Elle a fui à Paris en 1968, où elle s’est mariée à un mac qui l’a mise quatre ans sur le trottoir avant qu’elle ne divorce et ne trouve Ganay, bourgeois cossu qui veut bien l’entretenir. Depuis, elle s’est « rangée », elle travaille comme décoratrice. Ferrot, persuadé de l’amant caché, retourne dans l’appartement vidé par les flics pour l’explorer plus à fond. En le mettant à sac, il découvre, planqué dans le matelas, sous la moquette ou derrière les plinthes, des objets futiles de gamine qui joue au secret. Dont une photo d’elle posant devant un imposant manoir et une rare voiture de collection.
Ferrot va visiter les manoirs de la région pour le trouver. Hélas ! Il y en a trop, et il est pris par le temps. Il ne voit pas le bon, qui se trouve juste après le dernier visité. En se rendant dans la voiture de Ménard au tapissage ordonné par Ganay, qui veut le confondre car tous les indices mènent à lui, Ferrot se fait arrêter devant un supermarché où il doit acheter des bricoles, et « du chewing-gum pour le petit ». Étienne est le fils de Ménard qui ressemble à son père, assis sur la banquette arrière de la R6. Ferrot est reconnu aux caisses par le contrôleur qui fait la queue et laisse son caddie pour téléphoner à la police. Ferrot l’assomme entre deux camionnettes avant de prendre en main l’enquête. Ganay le confronte et lui avoue son crime alors que Ferrot lui met dans les mains la photo de Sylvia devant la Grenouillère et la voiture Nash. Dilemme.
Marc Ferrot est fidèle à son chef et à son épouse, la seule famille qu’il se soit trouvée. Dans le même temps, il est fonctionnaire de police et ne peut laisser un crime impuni. Ganay, une fois de plus, va faire basculer le destin. Il brûle la photo compromettante, le seul indice qui permettrait de le relier à Sylvia, et menace Ferrot de son pistolet. Celui-ci, par réflexe bien entraîné, dégaine son Python 357 et l’abat dans sa R5 noire. Lui est toujours vivant, mais a tué le seul qui pouvait l’innocenter. Sauf que Thérèse, l’épouse abandonnée de Ganay, ne veut plus vivre sans son mari, mais ne peut se résoudre à se tuer. Elle pique la Mercedes au chauffeur parti chercher des médicaments pour elle et attend Ferrot devant chez lui. Elle l’emmène sur les berges de la Loire, où son mari Ganay a jeté l’arme du crime, où il a avoué, où il a brûlé la photo et s’est fait tuer. Elle supplie Ferrot de l’aider à appuyer sur la détente pour en finir mais, auparavant, lui raconte tout ce que son mari lui a confié. Coup de pistolet – c’est fini. Une fois de plus, la Loire emporte les destins.
Ferrot a été reconnu par son portrait-robot, bien qu’il ait tenté de se défigurer au vitriol. Ménard l’a suivi pour le confondre, et a entendu la confession de Thérèse. Il sait que son inspecteur-chef est innocent. Mais voilà qu’il est convoqué en urgence devant le supermarché où des malfrats braquent la caisse, armés de pistolets-mitrailleurs. Sa R6 est criblée de balles, son coéquipier blessés. Ils ne peuvent sortir. Va-t-il se faire descendre et perdre tout espoir d’innocenter Ferrot ? Je laisse au spectateur le soin de le savoir, même si une ultime pirouette instille un doute.
J’ai vu plusieurs fois ce film au cours des années, et il ne m’a jamais déçu. Il est bien monté, bien joué, le suspense établi. Même si l’on connaît la fin, elle surprendra toujours. Les détails de l’époque sont aussi bien rendus. Nous plongeons dans une atmosphère, celle de la province assoupie où couvent des pulsions ardentes sous les apparences. Pour le commissaire la maison bourgeoise de centre-ville, le manoir à la campagne, la grosse voiture Mercedes, la petite R5 pour la ville, et la coquetterie américaine Nash. Pour l’inspecteur-chef la Peugeot 404 fonctionnelle, le revolver Python 357 et son sertisseur de balles, la maison de banlieue sans âme. Pour l’inspecteur la Renault 6 classe moyenne, le caté pour le gamin, la décoloration blonde pour l’épouse.
Et, dans la chambre de Thérèse comme lors de la visite à Paris de Ferrot avec Sylvia, la peinture de Jupiter et Sémélé de Gustave Moreau, allégorie du film. Sémélé est foudroyée par l’apparence de Jupiter transfiguré en jeune homme dans sa perfection. Cela par la jalousie de l’épouse de Jupiter…
DVD Police Python 357, Alain Corneau, 1976, avec Yves Montand, François Périer, Mathieu Carrière, Simone Signoret, Stefania Sandrelli,StudioCanal 2025, français, 2h02, €9,99, Blu-ray €14,99
(mon commentaire est libre, seuls les liens sont sponsorisés par amazon.fr)
#affection #alainCorneau #amant #années70 #assasin #atmosphère #bourgeois #catholique #Cinéma #commissaire #dvd #enquête #film #gamine #gustaveMoreau #infirme #innocent #inspecteur #jalousie #loire #manoir #orléans #portraitRobot #province #respectabilité #revolver #secrets #suicide #témoins #yvesMontand

#affection #alaincorneau #amant #annees70 #assasin #atmosphere
Ol Monk @[email protected]
2026-10-18

His name is Glumfry and I love him very much.

#Minipainting
#Nerdlings

#minipainting #nerdlings