Step 17 of 18 of the SQL tutorial. A CTE gives a result a name so the statement can read it twice. claims flattens every boiling point onto its compound and pressure; spread keeps the 53 compounds claimed at more than one pressure; the final SELECT joins claims to itself to put the two ends of the range on one line. DL-2-Phenylpropionic acid drops 145 °C — 260 °C at 760 mmHg, 115 °C at 1 mmHg. Write it without WITH and the flattening is pasted three times.
It opens on the query WITH claims AS ( SELECT c.id AS compound_id, c.preferred_name AS name, b.pressure_mmhg, MIN(b.low_c) AS low_c FROM boiling_points b JOIN catalog_entries e ON e.id = b.catalog_entry_id JOIN compounds c ON c.id = e.compound_id WHERE b.pressure_mmhg IS NOT NULL AND b.low_c IS NOT NULL GROUP BY c.id, b.pressure_mmhg ), spread AS ( SELECT compound_id, name, COUNT(*) AS nb_pressures, MAX(pressure_mmhg) AS highest_mmhg, MIN(pressure_mmhg) AS lowest_mmhg FROM claims GROUP BY compound_id HAVING nb_pressures > 1 ) SELECT s.name, s.nb_pressures, hi.low_c AS bp_at_highest, s.highest_mmhg, lo.low_c AS bp_at_lowest, s.lowest_mmhg, ROUND(hi.low_c - lo.low_c, 1) AS drop_c FROM spread s JOIN claims hi ON hi.compound_id = s.compound_id AND hi.pressure_mmhg = s.highest_mmhg JOIN claims lo ON lo.compound_id = s.compound_id AND lo.pressure_mmhg = s.lowest_mmhg ORDER BY drop_c DESC LIMIT 15, which you can run and change in place.
Ask the same question of a chemical dataset in SQL and in Mango and compare the two answers. The database is a SQLite file your browser downloads once and queries locally, so nothing you write ever leaves your machine — which is also why the tool needs JavaScript.