D1 sql, run 2: the query session

37 queries3 failed7 rounds

The opening prompt

## The data

You can't see the data, but you can query it. It is in DuckDB, in the tables below. Write DuckDB SQL.

To run queries, reply with one or more fenced blocks (```sql), and nothing else is needed:
each is run and its results sent back to you. You have up to 8 rounds of queries.
When you have what you need, reply with the final answers and no queries.

All columns are text (VARCHAR); dates are ISO strings.

- `institutions` (31 rows): institution_id, name, ror, country, type, parent_id
- `authors` (167 rows): author_id, name, orcid
- `sources` (14 rows): source_id, name, issn, type, tier
- `works` (850 rows): work_id, title, year, type, source_id, doi, version_of, is_retracted, record_source
- `authorships` (1532 rows): work_id, author_id, author_position, institution_id, affil_seq
- `citations` (1067 rows): citing_work_id, cited_work_id

## Questions

Answer each question below from the data above. Start each answer on a new line with its
number in bold, like **Q1.**, give the direct answer first, then at most two sentences of
reasoning that cite the records (IDs) you relied on.

Then end with one JSON block (```json) holding the structured answer to each question listed
here, with exactly these fields, shaped like {"Q1": {"field": value}, ...}. Name works, authors, institutions and sources by their record ID: the work_id, author_id, institution_id or source_id in the CSV files, or the part of the IRI after res: in the RDF (W01, A07, I02, S03; not their titles or names).

- Q1: count (a number)
- Q2: works (a list of record IDs)
- Q3: same ("yes", "no" or "depends")
- Q4: count (a number)
- Q5: same ("yes", "no" or "depends")
- Q6: author (a record ID)
- Q7: count (a number)
- Q8: parent (a record ID)
- Q9: count (a number)
- Q10: count (a number)
- Q11: count (a number)
- Q12: full (a number), fractional (a number)
- Q13: full (a record ID), fractional (a record ID)
- Q14: full (a number), fractional (a number)
- Q15: count (a number)
- Q16: count (a number)
- Q17: works (a list of record IDs)
- Q18: count (a number)
- Q19: count (a number)
- Q20: works (a list of record IDs)
- Q21: count (a number)
- Q22: more_in_total (a record ID), more_in_window (a record ID)
- Q23: total (a number), in_window (a number)
- Q24: count (a number)
- Q25: count (a number)
- Q26: counts ("yes", "no" or "depends")
- Q27: institution (a record ID), citing_works (a number)
- Q28: count (a number)
- Q29: author (a number), institution (a number), country (a number)
- Q30: count (a number)
- Q31: works (a list of record IDs)
- Q32: count (a number)
- Q33: work (a record ID)
- Q34: count (a number)
- Q35: count (a number)
- Q36: count (a number)
- Q37: institution (a record ID)
- Q38: institutions (a list of record IDs)
- Q39: count (a number)
- Q40: source (a record ID)
- Q41: higher ("yes", "no" or "depends")
- Q42: count (a number)
- Q43: research ("yes", "no" or "depends")
- Q44: institutions (a list of record IDs)
- Q45: count (a number)
- Q46: parent (a record ID)

Q1. How many distinct people do the author records named "Mara Ellison", "M. Ellison" and "Ellison, Mara J." stand for?
Q2. List every work, of any type, that Mara Ellison (A01) is an author of.
Q3. Is the Jonas Brandt who wrote W37 the same person as the Jonas Brandt who wrote W38?
Q4. How many research works has the Jonas Brandt of Albury Metropolitan University (A05) authored?
Q5. Is the M. Ellison who wrote W08 the same person as the Mara Ellison who wrote W01?
Q6. Which author has the most research works credited to the University of Albury, its units included?
Q7. How many research works has Mara Ellison authored?
Q8. What is the ultimate parent of the Tessel Laboratory for Ocean Sensing (I04)?
Q9. How many research works is the University of Albury (I02) credited with, its units included?
Q10. How many research works is the Albury University System (I01) credited with, all its units included?
Q11. How many research works is Kesting University (I07) credited with, its units included?
Q12. How many research works did Kesting University (I07, units included) publish in 2024, under full counting and under fractional counting (each work split equally among its authors)?
Q13. In 2024, which had more research works, Kesting University (I07) or Rostval Technical University (I09): under full counting, and under fractional counting?
Q14. How many research works did GB institutions publish in 2024, under full counting and under fractional counting (each work split equally among its authors, each author's share going to their institution's country)?
Q15. How many research works appeared in Ocean Sensing Letters (S02) in 2024?
Q16. How many research works appeared in the Journal of Marine Autonomy (S01)?
Q17. Which of Mara Ellison's works are not research output because of their document type?
Q18. How many distinct research works has Priya Ramanathan (A07) authored?
Q19. How many distinct works cite "Drift-aware mapping with low-cost sonar", in either of its versions?
Q20. Which works in the data are preprints of another work that is also in the data?
Q21. How many research works is Oskel Polytechnic (I14) credited with?
Q22. Which has more citations, W30 or W31 (counting both versions of each): in total, and within its publication year and the two years after?
Q23. How many citations has W30 received in total, and how many within its publication year and the two years after?
Q24. How many research works has Lena Vossberg (A09) authored?
Q25. What is the total of the citation counts of Lena Vossberg's research works?
Q26. Does W23 count towards Kesting University's research output?
Q27. Which other institution's works cite Kesting University's research works the most, each citing work counted once per institution, and how many of its works do?
Q28. How many distinct works from Rostval Technical University (I09) cite Kesting University's research works?
Q29. Of the works citing "Drift-aware mapping with low-cost sonar" (either version), how many are self-citations at the level of authors, of the University of Albury (units included), and of countries?
Q30. Of the works citing "Drift-aware mapping with low-cost sonar" (either version), how many are self-citations at the level of the Albury University System?
Q31. Which works cite at least one of the works that cite W01?
Q32. How many distinct works are within two citation hops of W01 (citing it, or citing a work that cites it)?
Q33. Which of Kesting University's research works has the most citations?
Q34. How many research works was Kesting University (units included) credited with in 2025?
Q35. How many research works is the Kesting Institute of Hydrodynamics (I08) credited with?
Q36. How many research works appeared in the Proceedings of the Hydrodynamics Forum (S06)?
Q37. Which institution is W70 credited to?
Q38. Which institutions is W71 credited to, at every level?
Q39. How many of the University of Albury's research works (units included) appeared in a source of the highest tier?
Q40. Which source is in the highest tier?
Q41. Is Coastal Engineering Review (S03) in a higher tier than Ocean Sensing Letters (S02)?
Q42. How many research works appeared in Global Marine Science Reports (S04)?
Q43. Is W07 research output?
Q44. Which institutions are part of the Albury University System, at any level below it?
Q45. How many research works is the Halloran Institute for Coastal Studies (I06) credited with?
Q46. What is the ultimate parent of the Halloran Institute for Coastal Studies (I06)?
Q47. Why do Kesting University and Rostval Technical University swap places in 2024 between full and fractional counting?
Q48. Why is W70 not one of the University of Albury's research works, though its author lists the Tessel Laboratory?

The model

I'll start by exploring the schema and the small tables.

```sql
SELECT * FROM institutions ORDER BY institution_id;
```
```sql
SELECT * FROM sources ORDER BY source_id;
```
```sql
SELECT * FROM authors WHERE name ILIKE '%ellison%' OR name ILIKE '%brandt%' OR name ILIKE '%vossberg%' OR name ILIKE '%ramanathan%' OR author_id IN ('A01','A05','A07','A09');
```
```sql
SELECT type, record_source, is_retracted, count(*), count(version_of) FROM works GROUP BY ALL ORDER BY ALL;
```

Query results

Query 1:

| institution_id | name | ror | country | type | parent_id |
|---|---|---|---|---|---|
| I01 | Albury University System | 0hygr4985 | GB | SYS |  |
| I02 | University of Albury | 06safhw22 | GB | UNI | I01 |
| I03 | Albury Centre for Marine Robotics | 06a9yq342 | GB | LAB | I02 |
| I04 | Tessel Laboratory for Ocean Sensing | 0002ezf81 | GB | LAB | I03 |
| I05 | Albury Metropolitan University | 0ww7kxb57 | GB | UNI | I01 |
| I06 | Halloran Institute for Coastal Studies | 0175f3924 | GB | AFF | I02 |
| I07 | Kesting University | 0593t4875 | DE | UNI |  |
| I08 | Kesting Institute of Hydrodynamics | 0brjdg570 | DE | LAB | I07 |
| I09 | Rostval Technical University | 0k8rh7879 | NL | UNI |  |
| I10 | Marrow Instruments BV | 0t3x83535 | NL | CO |  |
| I11 | Brevin Ocean Agency | 0wvack776 | FR | GOV |  |
| I12 | Université de Lorvain | 091pt9775 | FR | UNI |  |
| I13 | Dunmore College | 095cwy722 | GB | UNI |  |
| I14 | Oskel Polytechnic | 06t6n5818 | NL | UNI |  |
| I15 | Carrow State University | 0v91gp428 | US | UNI |  |
| I16 | Hoshimine Institute of Technology | 06afa6f88 | JP | UNI |  |
| I17 | Université de Mallois | 0pshcv029 | CA | UNI |  |
| I18 | Universidad de Ventolera | 0ay9jr885 | ES | UNI |  |
| I19 | Albury Laboratory for Acoustic Imaging | 0xe2n1515 | GB | LAB | I02 |
| I20 | Albury Metropolitan Centre for Port Studies | 0bp5b1111 | GB | LAB | I05 |
| I21 | Wexcombe Institute for Estuary Research | 0ms1zdt74 | GB | AFF | I05 |
| I22 | Sandhaven Institute for Wave Research | 0rn08b412 | DE | AFF | I07 |
| I23 | Rostval Laboratory for Offshore Structures | 0zekfva10 | NL | LAB | I09 |
| I24 | Pellmore University | 0n0j86p46 | US | UNI |  |
| I25 | Università di Calvessa | 08mz9rf83 | IT | UNI |  |
| I26 | Tarnowa University of Technology | 0189z6431 | PL | UNI |  |
| I27 | Kirrabay University | 0wam41j64 | AU | UNI |  |
| I28 | Nordvik University | 00etvqe29 | NO | UNI |  |
| I29 | Hanseo Ocean Institute | 0g18r1r20 | KR | GOV |  |
| I30 | Universidade de Arvela | 0ha4xax60 | PT | UNI |  |
| I31 | Corrimal Marine Systems Inc | 0nq63kw54 | US | CO |  |

Query 2:

| source_id | name | issn | type | tier |
|---|---|---|---|---|
| S01 | Journal of Marine Autonomy | 9866-3925 | JOU | K3 |
| S02 | Ocean Sensing Letters | 3195-4973 | JOU | K2 |
| S03 | Coastal Engineering Review | 1154-9679 | JOU | K1 |
| S04 | Global Marine Science Reports | 8864-5325 | JOU | K0 |
| S05 | OceanArXiv |  | REP |  |
| S06 | Proceedings of the Hydrodynamics Forum | 9804-2748 | JOU | K2 |
| S07 | Journal of Estuarine Dynamics | 1483-2835 | JOU | K2 |
| S08 | Hydrology Notes | 9832-2482 | JOU | K1 |
| S09 | Journal of Coastal Hydraulics | 3431-7424 | JOU | K2 |
| S10 | Marine Technology Bulletin | 7809-8515 | JOU | K1 |
| S11 | Annals of Ocean Observation | 8324-8964 | JOU | K2 |
| S12 | Shelf Sea Letters | 8103-2767 | JOU | K1 |
| S13 | International Journal of Ocean Frontiers | 1765-9671 | JOU | K0 |
| S14 | CoastRxiv |  | REP |  |

Query 3:

| author_id | name | orcid |
|---|---|---|
| A01 | Mara Ellison | 0000-0002-9841-6229 |
| A02 | M. Ellison | 0000-0002-9841-6229 |
| A03 | Ellison, Mara J. | 0000-0002-9841-6229 |
| A04 | M. Ellison | 0000-0004-8662-8360 |
| A05 | Jonas Brandt | 0000-0001-6105-5008 |
| A06 | Jonas Brandt | 0000-0007-5475-1779 |
| A07 | Priya Ramanathan | 0000-0009-2298-3695 |
| A09 | Lena Vossberg | 0000-0000-8073-7028 |
| A101 | M. Ellison | 0000-0002-9841-6229 |
| A102 | M. Ellison | 0000-0008-4738-8328 |
| A103 | L. Vossberg | 0000-0000-8073-7028 |
| A104 | J. Brandt | 0000-0001-6105-5008 |
| A105 | Jonas Brandt | 0000-0004-9046-7961 |
| A108 | Priya Ramanathan | 0000-0007-1802-8393 |

Query 4:

| type | record_source | is_retracted | count_star() | count(version_of) |
|---|---|---|---|---|
| ART | CRIS | false | 3 | 0 |
| ART | FEED | false | 637 | 0 |
| ART | FEED | true | 13 | 0 |
| ART | STG | false | 16 | 0 |
| EDT | FEED | false | 33 | 0 |
| ERR | FEED | false | 8 | 0 |
| PPR | FEED | false | 34 | 23 |
| PTX | FEED | false | 12 | 0 |
| REV | FEED | false | 73 | 0 |
| REV | STG | false | 3 | 0 |
| SUP | FEED | false | 12 | 0 |
| SUP | STG | false | 6 | 0 |

Run more queries, or give the final answers.

The model

```sql
SELECT type, record_source, min(work_id), max(work_id), min(title), max(title), min(year), max(year), count(DISTINCT source_id) FROM works GROUP BY ALL ORDER BY ALL;
```
```sql
SELECT * FROM works WHERE type IN ('PTX','SUP','ERR','EDT') OR record_source IN ('CRIS','STG') ORDER BY type, work_id LIMIT 80;
```
```sql
SELECT * FROM works WHERE work_id IN ('W01','W07','W08','W23','W30','W31','W37','W38','W70','W71') OR version_of IN ('W30','W31','W01') ORDER BY work_id;
```
```sql
SELECT a.*, w.type, w.year, w.record_source FROM authorships a JOIN works w USING(work_id) WHERE a.author_id IN ('A01','A02','A03','A04','A101','A102','A05','A06','A104','A105','A07','A108','A09','A103') ORDER BY a.author_id, a.work_id;
```

Query results

Query 1:

| type | record_source | min(work_id) | max(work_id) | min(title) | max(title) | min("year") | max("year") | count(DISTINCT source_id) |
|---|---|---|---|---|---|---|---|---|
| ART | CRIS | W12 | W73 | Glider-borne turbidity sensing | Sonar mosaics from glider fleets | 2022 | 2025 | 1 |
| ART | FEED | W01 | W998 | A community intercomparison of beach profiles | Wave-current interaction over sandbars | 2019 | 2025 | 12 |
| ART | STG | W268 | W987 | A framework for fleet coordination in the Brevin shelf | Sediment transport in tidal inlets | 2025 | 2025 | 4 |
| EDT | FEED | W04 | W991 | Editorial: a model of coastal upwelling in tidal inlets | Foreword: tidal mixing | 2019 | 2025 | 10 |
| ERR | FEED | W05 | W949 | Erratum: A model of breaking waves in the North Sea | Erratum: Uncertainty in internal waves estimates in the North Sea | 2024 | 2025 | 4 |
| PPR | FEED | W02 | W877 | A framework for fleet coordination | Uncertainty in wave boundary layers estimates | 2019 | 2025 | 2 |
| PTX | FEED | W09 | W891 | Front matter | Table of contents | 2019 | 2025 | 4 |
| REV | FEED | W1006 | W995 | A framework for harbour seiches in a macrotidal bay: a review | Uncertainty in wave runup estimates in tidal inlets: a review | 2019 | 2025 | 12 |
| REV | STG | W573 | W92 | Coastal turbulence: a review | Seasonal variability in breaking waves | 2025 | 2025 | 3 |
| SUP | FEED | W1002 | W951 | A framework for dune erosion | Vortex shedding in shallow channels | 2019 | 2025 | 2 |
| SUP | STG | W372 | W997 | A framework for channel hydraulics in fjords | Uncertainty in turbulent wakes estimates in sandy coasts | 2025 | 2025 | 3 |

Query 2:

| work_id | title | year | type | source_id | doi | version_of | is_retracted | record_source |
|---|---|---|---|---|---|---|---|---|
| W12 | Sonar mosaics from glider fleets | 2024 | ART | S02 | 10.55555/s02.2024.3526 |  | false | CRIS |
| W19 | Glider-borne turbidity sensing | 2025 | ART | S02 | 10.55555/s02.2025.2198 |  | false | CRIS |
| W268 | Remote sensing of turbulent wakes in a laboratory flume | 2025 | ART | S02 | 10.55555/s02.2025.4471 |  | false | STG |
| W329 | Field measurements of channel hydraulics in mixed-energy coasts | 2025 | ART | S01 | 10.55555/s01.2025.8849 |  | false | STG |
| W397 | Scaling laws for acoustic positioning | 2025 | ART | S01 | 10.55555/s01.2025.9888 |  | false | STG |
| W488 | A framework for turbulent wakes | 2025 | ART | S01 | 10.55555/s01.2025.9949 |  | false | STG |
| W569 | A framework for fleet coordination in the Brevin shelf | 2025 | ART | S02 | 10.55555/s02.2025.9174 |  | false | STG |
| W582 | Field measurements of sediment resuspension in a semi-enclosed sea | 2025 | ART | S03 | 10.55555/s03.2025.3135 |  | false | STG |
| W610 | Scaling laws for estuarine plumes in the open shelf | 2025 | ART | S02 | 10.55555/s02.2025.5434 |  | false | STG |
| W700 | Observations of stratified flows | 2025 | ART | S03 | 10.55555/s03.2025.3995 |  | false | STG |
| W714 | Low-cost methods for wave boundary layers in the Brevin shelf | 2025 | ART | S02 | 10.55555/s02.2025.9707 |  | false | STG |
| W73 | Saltmarsh retreat on the Halloran shore | 2022 | ART | S02 | 10.55555/s02.2022.7603 |  | false | CRIS |
| W802 | Observations of estuarine plumes in a laboratory flume | 2025 | ART | S02 | 10.55555/s02.2025.8136 |  | false | STG |
| W844 | A framework for seabed classification in fjords | 2025 | ART | S01 | 10.55555/s01.2025.4503 |  | false | STG |
| W868 | Scaling laws for breaking waves | 2025 | ART | S01 | 10.55555/s01.2025.8064 |  | false | STG |
| W878 | A model of tidal asymmetry | 2025 | ART | S08 | 10.55555/s08.2025.8372 |  | false | STG |
| W90 | Sediment transport in tidal inlets | 2025 | ART | S02 | 10.55555/s02.2025.8585 |  | false | STG |
| W91 | Closure models for wave boundary layers | 2025 | ART | S01 | 10.55555/s01.2025.4607 |  | false | STG |
| W987 | Machine learning for stratified flows | 2025 | ART | S02 | 10.55555/s02.2025.6401 |  | false | STG |
| W04 | Editorial: a new decade of marine autonomy | 2023 | EDT | S01 | 10.55555/s01.2023.1032 |  | false | FEED |
| W1034 | Foreword: tidal mixing | 2025 | EDT | S06 | 10.55555/s06.2025.1338 |  | false | FEED |
| W1041 | Editorial: survey vessel autonomy | 2025 | EDT | S03 | 10.55555/s03.2025.1894 |  | false | FEED |
| W1043 | Editorial: machine learning for sediment flux | 2023 | EDT | S08 | 10.55555/s08.2023.2973 |  | false | FEED |
| W110 | Editorial: beach profiles | 2021 | EDT | S07 | 10.55555/s07.2021.3358 |  | false | FEED |
| W123 | Editorial: river plumes | 2023 | EDT | S08 | 10.55555/s08.2023.3508 |  | false | FEED |
| W224 | Editorial: scaling laws for harbour seiches | 2025 | EDT | S09 | 10.55555/s09.2025.4466 |  | false | FEED |
| W271 | Editorial: battery endurance | 2019 | EDT | S01 | 10.55555/s01.2019.6724 |  | false | FEED |
| W331 | Editorial: ocean optics | 2019 | EDT | S02 | 10.55555/s02.2019.4747 |  | false | FEED |
| W371 | Editorial: a model of coastal upwelling in tidal inlets | 2024 | EDT | S10 | 10.55555/s10.2024.9940 |  | false | FEED |
| W418 | Editorial: dune erosion | 2024 | EDT | S02 | 10.55555/s02.2024.3216 |  | false | FEED |
| W431 | Editorial: revisiting dune erosion | 2025 | EDT | S11 | 10.55555/s11.2025.5917 |  | false | FEED |
| W437 | Editorial: low-cost methods for beach profiles in shallow channels | 2024 | EDT | S08 | 10.55555/s08.2024.9419 |  | false | FEED |
| W480 | Editorial: ocean optics | 2019 | EDT | S03 | 10.55555/s03.2019.8946 |  | false | FEED |
| W548 | Editorial: observations of tidal asymmetry in the North Sea | 2021 | EDT | S09 | 10.55555/s09.2021.1082 |  | false | FEED |
| W572 | Editorial: seasonal variability in estuarine salinity | 2019 | EDT | S10 | 10.55555/s10.2019.1114 |  | false | FEED |
| W626 | Editorial: beach profiles | 2019 | EDT | S02 | 10.55555/s02.2019.6520 |  | false | FEED |
| W637 | Editorial: a model of river plumes in the Albury estuary | 2024 | EDT | S12 | 10.55555/s12.2024.2287 |  | false | FEED |
| W674 | Foreword: stratified flows | 2022 | EDT | S06 | 10.55555/s06.2022.6198 |  | false | FEED |
| W719 | Editorial: long-term trends in wave runup | 2025 | EDT | S07 | 10.55555/s07.2025.8025 |  | false | FEED |
| W751 | Editorial: sensor calibration | 2020 | EDT | S03 | 10.55555/s03.2020.2526 |  | false | FEED |
| W756 | Editorial: low-cost methods for marsh accretion in tidal inlets | 2020 | EDT | S08 | 10.55555/s08.2020.8771 |  | false | FEED |
| W76 | Foreword to the Forum proceedings | 2025 | EDT | S06 | 10.55555/s06.2025.4219 |  | false | FEED |
| W786 | Editorial: observations of storm surge in fjords | 2020 | EDT | S08 | 10.55555/s08.2020.6266 |  | false | FEED |
| W882 | Editorial: stratified flows | 2020 | EDT | S01 | 10.55555/s01.2020.7459 |  | false | FEED |
| W901 | Editorial: seasonal variability in dune erosion in sandy coasts | 2020 | EDT | S07 | 10.55555/s07.2020.7502 |  | false | FEED |
| W932 | Editorial: machine learning for wave runup in the Wexcombe marshes | 2021 | EDT | S11 | 10.55555/s11.2021.6896 |  | false | FEED |
| W936 | Editorial: fleet coordination | 2019 | EDT | S01 | 10.55555/s01.2019.1595 |  | false | FEED |
| W959 | Editorial: long-term trends in wave runup in sandy coasts | 2022 | EDT | S12 | 10.55555/s12.2022.2759 |  | false | FEED |
| W966 | Editorial: scaling laws for river plumes in fjords | 2024 | EDT | S07 | 10.55555/s07.2024.3221 |  | false | FEED |
| W976 | Foreword: sediment resuspension | 2021 | EDT | S06 | 10.55555/s06.2021.6763 |  | false | FEED |
| W989 | Editorial: long-term trends in marsh accretion in the Wexcombe marshes | 2020 | EDT | S08 | 10.55555/s08.2020.1702 |  | false | FEED |
| W991 | Editorial: revisiting estuarine salinity in a laboratory flume | 2023 | EDT | S07 | 10.55555/s07.2023.7046 |  | false | FEED |
| W05 | Erratum: Drift-aware mapping with low-cost sonar | 2024 | ERR | S02 | 10.55555/s02.2024.7136 |  | false | FEED |
| W321 | Erratum: A model of breaking waves in the North Sea | 2025 | ERR | S03 | 10.55555/s03.2025.6357 |  | false | FEED |
| W481 | Erratum: Machine learning for sensor calibration in a laboratory flume | 2024 | ERR | S09 | 10.55555/s09.2024.5830 |  | false | FEED |
| W766 | Erratum: A model of fleet coordination in mixed-energy coasts: a review | 2025 | ERR | S09 | 10.55555/s09.2025.9163 |  | false | FEED |
| W810 | Erratum: Uncertainty in internal waves estimates in the North Sea | 2025 | ERR | S02 | 10.55555/s02.2025.6696 |  | false | FEED |
| W813 | Erratum: Remote sensing of buoy telemetry in fjords | 2025 | ERR | S02 | 10.55555/s02.2025.2143 |  | false | FEED |
| W914 | Erratum: Field measurements of channel hydraulics: a review | 2025 | ERR | S06 | 10.55555/s06.2025.1637 |  | false | FEED |
| W949 | Erratum: Observations of survey vessel autonomy in tidal inlets | 2024 | ERR | S02 | 10.55555/s02.2024.1996 |  | false | FEED |
(20 more rows not shown)

Query 3:

| work_id | title | year | type | source_id | doi | version_of | is_retracted | record_source |
|---|---|---|---|---|---|---|---|---|
| W01 | Adaptive path planning for underwater gliders | 2021 | ART | S01 | 10.55555/s01.2021.1465 |  | false | FEED |
| W07 | Low-cost sonar arrays for coastal monitoring | 2020 | ART | S04 | 10.55555/s04.2020.7029 |  | false | FEED |
| W08 | Turbulent wake modelling behind tidal turbines | 2025 | ART | S01 | 10.55555/s01.2025.5958 |  | false | FEED |
| W23 | Anomalous dissipation in estuarine plumes | 2023 | ART | S02 | 10.55555/s02.2023.2925 |  | true | FEED |
| W30 | Tidal stream resource maps for the Dunmore coast | 2019 | ART | S03 | 10.55555/s03.2019.9502 |  | false | FEED |
| W31 | Machine-learned tidal forecasts | 2023 | ART | S01 | 10.55555/s01.2023.5047 |  | false | FEED |
| W37 | Port traffic and seabed disturbance | 2022 | ART | S03 | 10.55555/s03.2022.9107 |  | false | FEED |
| W38 | Seagrass mapping from aerial imagery | 2023 | ART | S02 | 10.55555/s02.2023.6112 |  | false | FEED |
| W42 | Machine-learned tidal forecasts | 2023 | PPR | S05 | 10.55555/s05.2023.6876 | W31 | false | FEED |
| W70 | Optical backscatter in turbid estuaries | 2023 | ART | S02 | 10.55555/s02.2023.6434 |  | false | FEED |
| W71 | Low-power optical sensors for gliders | 2024 | ART | S01 | 10.55555/s01.2024.6287 |  | false | FEED |

Query 4:

| work_id | author_id | author_position | institution_id | affil_seq | type | year | record_source |
|---|---|---|---|---|---|---|---|
| W01 | A01 | 1 | I02 | 1 | ART | 2021 | FEED |
| W04 | A01 | 1 | I02 | 1 | EDT | 2023 | FEED |
| W05 | A01 | 1 | I02 | 1 | ERR | 2024 | FEED |
| W06 | A01 | 1 | I02 | 1 | ART | 2024 | FEED |
| W1005 | A01 | 1 | I02 | 1 | ART | 2025 | FEED |
| W12 | A01 | 1 | I02 | 1 | ART | 2024 | CRIS |
| W238 | A01 | 1 | I02 | 1 | ART | 2023 | FEED |
| W271 | A01 | 1 | I02 | 1 | EDT | 2019 | FEED |
| W374 | A01 | 1 | I02 | 1 | ART | 2023 | FEED |
| W464 | A01 | 1 | I02 | 1 | ART | 2021 | FEED |
| W676 | A01 | 1 | I02 | 1 | ART | 2025 | FEED |
| W813 | A01 | 1 | I02 | 1 | ERR | 2025 | FEED |
| W949 | A01 | 1 | I02 | 1 | ERR | 2024 | FEED |
| W02 | A02 | 1 | I02 | 1 | PPR | 2022 | FEED |
| W07 | A02 | 1 | I02 | 1 | ART | 2020 | FEED |
| W1048 | A02 | 1 | I02 | 1 | ART | 2023 | FEED |
| W1050 | A02 | 1 | I02 | 1 | ART | 2024 | FEED |
| W19 | A02 | 1 | I02 | 1 | ART | 2025 | CRIS |
| W449 | A02 | 1 | I02 | 1 | ART | 2022 | FEED |
| W663 | A02 | 1 | I02 | 1 | PPR | 2021 | FEED |
| W869 | A02 | 1 | I02 | 1 | ART | 2023 | FEED |
| W972 | A02 | 1 | I02 | 1 | ART | 2020 | FEED |
| W03 | A03 | 1 | I02 | 1 | ART | 2023 | FEED |
| W206 | A03 | 1 | I02 | 1 | ART | 2020 | FEED |
| W763 | A03 | 1 | I02 | 1 | ART | 2024 | FEED |
| W817 | A03 | 1 | I02 | 1 | ART | 2023 | FEED |
| W08 | A04 | 1 | I07 | 1 | ART | 2025 | FEED |
| W1020 | A04 | 1 | I07 | 1 | ART | 2020 | FEED |
| W16 | A04 | 1 | I07 | 1 | ART | 2025 | FEED |
| W428 | A04 | 1 | I07 | 1 | ART | 2022 | FEED |
| W603 | A04 | 1 | I07 | 1 | ART | 2025 | FEED |
| W720 | A04 | 1 | I07 | 1 | ART | 2019 | FEED |
| W890 | A04 | 1 | I07 | 1 | ART | 2023 | FEED |
| W985 | A04 | 1 | I07 | 1 | ART | 2023 | FEED |
| W998 | A04 | 1 | I07 | 1 | ART | 2022 | FEED |
| W1038 | A05 | 1 | I05 | 1 | ART | 2020 | FEED |
| W14 | A05 | 1 | I05 | 1 | ART | 2025 | FEED |
| W37 | A05 | 1 | I05 | 1 | ART | 2022 | FEED |
| W554 | A05 | 1 | I05 | 1 | ART | 2024 | FEED |
| W565 | A05 | 1 | I05 | 1 | ART | 2019 | FEED |
| W785 | A05 | 1 | I05 | 1 | REV | 2020 | FEED |
| W792 | A05 | 1 | I05 | 1 | ART | 2023 | FEED |
| W1031 | A06 | 1 | I12 | 1 | ART | 2025 | FEED |
| W28 | A06 | 4 | I12 | 1 | ART | 2024 | FEED |
| W34 | A06 | 1 | I12 | 1 | ART | 2025 | FEED |
| W38 | A06 | 1 | I12 | 1 | ART | 2023 | FEED |
| W384 | A06 | 1 | I12 | 1 | ART | 2025 | FEED |
| W525 | A06 | 1 | I12 | 1 | ART | 2024 | FEED |
| W835 | A06 | 1 | I12 | 1 | ART | 2020 | FEED |
| W01 | A07 | 2 | I03 | 1 | ART | 2021 | FEED |
| W02 | A07 | 2 | I03 | 1 | PPR | 2022 | FEED |
| W03 | A07 | 2 | I03 | 1 | ART | 2023 | FEED |
| W05 | A07 | 2 | I03 | 1 | ERR | 2024 | FEED |
| W13 | A07 | 1 | I03 | 1 | ART | 2024 | FEED |
| W219 | A07 | 1 | I03 | 1 | PPR | 2022 | FEED |
| W251 | A07 | 1 | I03 | 1 | ART | 2023 | FEED |
| W262 | A07 | 1 | I03 | 1 | ART | 2025 | FEED |
| W292 | A07 | 1 | I03 | 1 | ART | 2020 | FEED |
| W293 | A07 | 1 | I03 | 1 | ART | 2023 | FEED |
| W435 | A07 | 1 | I03 | 1 | ART | 2023 | FEED |
(58 more rows not shown)

Run more queries, or give the final answers.

The model

<parameter name="sql">SELECT * FROM authorships WHERE work_id IN ('W70','W71','W23','W07','W08','W30','W31','W42','W12','W73') ORDER BY work_id, author_position, affil_seq;</sql>
<sql>SELECT affil_seq, count(*), count(institution_id) FROM authorships GROUP BY ALL ORDER BY ALL;</sql>
<sql>SELECT w.type, w.is_retracted, count(*) n, count(DISTINCT a.work_id) FROM works w LEFT JOIN authorships a USING(work_id) GROUP BY ALL ORDER BY ALL;</sql>
<sql>SELECT c.*, w.title, w.year, w.type FROM citations c JOIN works w ON w.work_id=c.citing_work_id WHERE cited_work_id IN ('W30','W31','W42') ORDER BY cited_work_id, year;</sql>
<sql>SELECT count(*), count(DISTINCT citing_work_id||cited_work_id) FROM citations; SELECT * FROM works WHERE title ILIKE '%Drift-aware%' OR work_id='W01';</sql>
```

Oops, formatting: I should use ```sql fences.


<parameter name="sql">SELECT 1;</parameter>
</sql>

Let me redo properly.

```sql
SELECT * FROM authorships WHERE work_id IN ('W70','W71','W23','W07','W08','W30','W31','W42','W12','W73') ORDER BY work_id, author_position, affil_seq;
```
```sql
SELECT affil_seq, count(*), count(institution_id) FROM authorships GROUP BY ALL ORDER BY ALL;
```
```sql
SELECT w.type, w.is_retracted, count(*) n, count(DISTINCT a.work_id) FROM works w LEFT JOIN authorships a USING(work_id) GROUP BY ALL ORDER BY ALL;
```
```sql
SELECT c.*, w.title, w.year, w.type FROM citations c JOIN works w ON w.work_id=c.citing_work_id WHERE cited_work_id IN ('W30','W31','W42') ORDER BY cited_work_id, year;
```
```sql
SELECT * FROM works WHERE title ILIKE '%Drift-aware%' OR version_of IN (SELECT work_id FROM works WHERE title ILIKE '%Drift-aware%');
```

Query results

Query 1 failed: Parser Error: syntax error at or near "fences"

Query 2:

| affil_seq | count_star() | count(institution_id) |
|---|---|---|
| 1 | 1506 | 1506 |
| 2 | 26 | 26 |

Query 3:

| type | is_retracted | n | count(DISTINCT a.work_id) |
|---|---|---|---|
| ART | false | 1233 | 656 |
| ART | true | 15 | 13 |
| EDT | false | 49 | 29 |
| ERR | false | 18 | 8 |
| PPR | false | 60 | 34 |
| PTX | false | 12 | 0 |
| REV | false | 136 | 76 |
| SUP | false | 25 | 18 |

Query 4:

| citing_work_id | cited_work_id | title | year | type |
|---|---|---|---|---|
| W60 | W30 | Remote sensing of dune erosion | 2020 | ART |
| W988 | W30 | Observations of harbour seiches in the open shelf | 2021 | ART |
| W439 | W30 | Seasonal variability in wave runup in the Wexcombe marshes | 2022 | ART |
| W61 | W30 | Uncertainty in marsh accretion estimates | 2022 | ART |
| W276 | W30 | A model of dune erosion in the open shelf | 2022 | ART |
| W289 | W30 | Revisiting tidal asymmetry in the open shelf | 2022 | ART |
| W334 | W30 | A model of tidal asymmetry in a semi-enclosed sea | 2023 | ART |
| W62 | W30 | Seasonal variability in dune erosion | 2023 | ART |
| W246 | W30 | Low-cost methods for wave runup in a semi-enclosed sea | 2023 | ART |
| W320 | W30 | Field measurements of coastal upwelling in the Brevin shelf | 2024 | ART |
| W894 | W30 | Revisiting harbour seiches | 2024 | ART |
| W856 | W30 | Observations of estuarine salinity in tidal inlets | 2024 | ART |
| W63 | W30 | Seasonal variability in sediment flux | 2024 | ART |
| W274 | W30 | Remote sensing of harbour seiches | 2024 | ART |
| W992 | W30 | Field measurements of harbour seiches in a laboratory flume | 2025 | ART |
| W1033 | W30 | Machine learning for tidal asymmetry | 2025 | ART |
| W64 | W30 | Remote sensing of internal waves | 2025 | ART |
| W878 | W30 | A model of tidal asymmetry | 2025 | ART |
| W62 | W31 | Seasonal variability in dune erosion | 2023 | ART |
| W851 | W31 | Uncertainty in dune erosion estimates | 2023 | ART |
| W1039 | W31 | Revisiting vortex shedding in sandy coasts | 2023 | ART |
| W320 | W31 | Field measurements of coastal upwelling in the Brevin shelf | 2024 | ART |
| W65 | W31 | Observations of dune erosion | 2024 | ART |
| W650 | W31 | Field measurements of wave runup in a macrotidal bay | 2024 | ART |
| W794 | W31 | Revisiting internal waves in tidal inlets | 2025 | ART |
| W318 | W31 | A framework for internal waves | 2025 | ART |
| W878 | W31 | A model of tidal asymmetry | 2025 | ART |
| W66 | W31 | Remote sensing of wave runup | 2025 | ART |
| W757 | W42 | Machine learning for tidal asymmetry in tidal inlets | 2023 | ART |
| W67 | W42 | Seasonal variability in tidal asymmetry | 2023 | ART |
| W1039 | W42 | Revisiting vortex shedding in sandy coasts | 2023 | ART |
| W249 | W42 | Observations of stratified flows in tidal inlets | 2025 | ART |

Query 5:

| work_id | title | year | type | source_id | doi | version_of | is_retracted | record_source |
|---|---|---|---|---|---|---|---|---|
| W02 | Drift-aware mapping with low-cost sonar | 2022 | PPR | S05 | 10.55555/s05.2022.2911 | W03 | false | FEED |
| W03 | Drift-aware mapping with low-cost sonar | 2023 | ART | S02 | 10.55555/s02.2023.9437 |  | false | FEED |
| W05 | Erratum: Drift-aware mapping with low-cost sonar | 2024 | ERR | S02 | 10.55555/s02.2024.7136 |  | false | FEED |

Query 6 failed: Parser Error: syntax error at or near "</"

Run more queries, or give the final answers.

The model

<parameter name="sql">SELECT * FROM authorships WHERE work_id IN ('W70','W71','W23','W07','W08','W30','W31','W42','W12','W73','W19') ORDER BY work_id, author_position, affil_seq;</parameter>
```sql
SELECT * FROM authorships WHERE work_id IN ('W70','W71','W23','W07','W08','W30','W31','W42','W12','W73','W19') ORDER BY work_id, author_position, affil_seq;
```
```sql
SELECT a.work_id, w.type, w.year, w.record_source, w.is_retracted, a.institution_id FROM authorships a JOIN works w USING(work_id) WHERE w.record_source IN ('CRIS','STG') ORDER BY 1 LIMIT 40;
```
```sql
SELECT doi, count(*), min(work_id), max(work_id) FROM works GROUP BY doi HAVING count(*)>1;
```
```sql
SELECT p.work_id ppr, p.version_of, w.type, w.title=p.title same_title FROM works p JOIN works w ON w.work_id=p.version_of WHERE p.version_of IS NOT NULL AND p.version_of<>'' ORDER BY 1;
```

Query results

Query 1:

| work_id | author_id | author_position | institution_id | affil_seq |
|---|---|---|---|---|
| W07 | A02 | 1 | I02 | 1 |
| W08 | A04 | 1 | I07 | 1 |
| W12 | A01 | 1 | I02 | 1 |
| W12 | A16 | 2 | I05 | 1 |
| W19 | A02 | 1 | I02 | 1 |
| W23 | A09 | 1 | I07 | 1 |
| W30 | A15 | 1 | I13 | 1 |
| W31 | A17 | 1 | I14 | 1 |
| W42 | A17 | 1 | I14 | 1 |
| W70 | A08 | 1 | I04 | 1 |
| W70 | A08 | 1 | I06 | 2 |
| W71 | A08 | 1 | I06 | 1 |
| W71 | A08 | 1 | I04 | 2 |
| W73 | A08 | 1 | I06 | 1 |

Query 2:

| work_id | type | year | record_source | is_retracted | institution_id |
|---|---|---|---|---|---|
| W12 | ART | 2024 | CRIS | false | I02 |
| W12 | ART | 2024 | CRIS | false | I05 |
| W19 | ART | 2025 | CRIS | false | I02 |
| W268 | ART | 2025 | STG | false | I07 |
| W268 | ART | 2025 | STG | false | I07 |
| W329 | ART | 2025 | STG | false | I07 |
| W372 | SUP | 2025 | STG | false | I07 |
| W397 | ART | 2025 | STG | false | I07 |
| W488 | ART | 2025 | STG | false | I07 |
| W569 | ART | 2025 | STG | false | I07 |
| W573 | REV | 2025 | STG | false | I07 |
| W582 | ART | 2025 | STG | false | I07 |
| W610 | ART | 2025 | STG | false | I07 |
| W680 | REV | 2025 | STG | false | I07 |
| W700 | ART | 2025 | STG | false | I07 |
| W714 | ART | 2025 | STG | false | I07 |
| W714 | ART | 2025 | STG | false | I07 |
| W73 | ART | 2022 | CRIS | false | I06 |
| W734 | SUP | 2025 | STG | false | I07 |
| W802 | ART | 2025 | STG | false | I07 |
| W802 | ART | 2025 | STG | false | I07 |
| W819 | SUP | 2025 | STG | false | I07 |
| W844 | ART | 2025 | STG | false | I07 |
| W866 | SUP | 2025 | STG | false | I07 |
| W868 | ART | 2025 | STG | false | I07 |
| W878 | ART | 2025 | STG | false | I07 |
| W90 | ART | 2025 | STG | false | I07 |
| W91 | ART | 2025 | STG | false | I07 |
| W92 | REV | 2025 | STG | false | I07 |
| W971 | SUP | 2025 | STG | false | I07 |
| W987 | ART | 2025 | STG | false | I07 |
| W997 | SUP | 2025 | STG | false | I07 |

Query 3:

(no rows)

Query 4:

| ppr | version_of | type | same_title |
|---|---|---|---|
| W02 | W03 | ART | True |
| W1049 | W789 | ART | True |
| W210 | W902 | ART | True |
| W219 | W251 | ART | True |
| W226 | W710 | ART | True |
| W263 | W279 | ART | True |
| W322 | W453 | ART | True |
| W407 | W1027 | REV | True |
| W416 | W640 | ART | True |
| W42 | W31 | ART | True |
| W432 | W879 | ART | True |
| W460 | W496 | ART | True |
| W506 | W422 | ART | True |
| W522 | W312 | ART | True |
| W524 | W807 | ART | True |
| W545 | W742 | ART | True |
| W556 | W1038 | ART | True |
| W611 | W301 | ART | True |
| W612 | W869 | ART | True |
| W663 | W464 | ART | True |
| W728 | W665 | ART | True |
| W730 | W303 | ART | True |
| W877 | W965 | ART | True |

Run more queries, or give the final answers.

The model

```sql
CREATE TABLE anc AS WITH RECURSIVE t(id,anc) AS (SELECT institution_id,institution_id FROM institutions UNION ALL SELECT t.id, i.parent_id FROM t JOIN institutions i ON i.institution_id=t.anc WHERE i.parent_id IS NOT NULL AND i.parent_id<>'' AND i.type<>'AFF') SELECT * FROM t;
```
```sql
CREATE TABLE ancf AS WITH RECURSIVE t(id,anc) AS (SELECT institution_id,institution_id FROM institutions UNION ALL SELECT t.id, i.parent_id FROM t JOIN institutions i ON i.institution_id=t.anc WHERE i.parent_id IS NOT NULL AND i.parent_id<>'') SELECT * FROM t;
```
```sql
CREATE TABLE rw AS SELECT * FROM works WHERE type IN ('ART','REV') AND is_retracted='false';
```
```sql
SELECT s.work_id, f.work_id f, s.year, f.year fy, s.title FROM works s JOIN works f ON f.title=s.title AND f.work_id<>s.work_id WHERE s.record_source='STG' LIMIT 20;
```
```sql
SELECT x.anc inst, count(DISTINCT r.work_id) strict_all, count(DISTINCT r.work_id) FILTER (WHERE record_source<>'STG') strict_noSTG, count(DISTINCT r.work_id) FILTER (WHERE year='2024') y24, count(DISTINCT r.work_id) FILTER (WHERE year='2025') y25, count(DISTINCT r.work_id) FILTER (WHERE year='2025' AND record_source<>'STG') y25noSTG
FROM rw r JOIN authorships a USING(work_id) JOIN anc x ON x.id=a.institution_id WHERE x.anc IN ('I01','I02','I06','I07','I08','I09','I14') GROUP BY 1 ORDER BY 1;
```
```sql
SELECT x.anc inst, count(DISTINCT r.work_id) full_roll FROM rw r JOIN authorships a USING(work_id) JOIN ancf x ON x.id=a.institution_id WHERE x.anc IN ('I01','I02','I05','I07') GROUP BY 1 ORDER BY 1;
```
```sql
WITH n AS (SELECT work_id, count(DISTINCT author_id) na FROM authorships GROUP BY 1), k AS (SELECT work_id, author_id, count(*) nf FROM authorships GROUP BY 1,2), w AS (SELECT a.work_id, a.author_id, a.institution_id, 1.0/n.na/k.nf sh FROM authorships a JOIN n USING(work_id) JOIN k USING(work_id,author_id) JOIN rw USING(work_id) WHERE rw.year='2024')
SELECT x.anc, count(DISTINCT w.work_id) fullc, round(sum(sh),4) frac FROM w JOIN anc x ON x.id=w.institution_id WHERE x.anc IN ('I07','I09') GROUP BY 1
UNION ALL
SELECT 'GB', count(DISTINCT w.work_id), round(sum(sh),4) FROM w JOIN institutions i ON i.institution_id=w.institution_id WHERE i.country='GB';
```

Query results

Query 1:

| Count |
|---|
| 44 |

Query 2:

| Count |
|---|
| 49 |

Query 3:

| Count |
|---|
| 732 |

Query 4:

(no rows)

Query 5:

| inst | strict_all | strict_noSTG | y24 | y25 | y25noSTG |
|---|---|---|---|---|---|
| I01 | 155 | 155 | 35 | 18 | 18 |
| I02 | 106 | 106 | 26 | 14 | 14 |
| I06 | 22 | 22 | 3 | 3 | 3 |
| I07 | 77 | 58 | 13 | 25 | 6 |
| I08 | 24 | 24 | 6 | 2 | 2 |
| I09 | 47 | 47 | 16 | 4 | 4 |
| I14 | 46 | 46 | 16 | 6 | 6 |

Query 6:

| inst | full_roll |
|---|---|
| I01 | 171 |
| I02 | 116 |
| I05 | 56 |
| I07 | 84 |

Query 7:

| anc | fullc | frac |
|---|---|---|
| I09 | 16 | 4.0 |
| I07 | 13 | 11.0 |
| GB | 47 | 43.0 |

Run more queries, or give the final answers.

The model

```sql
WITH p AS (SELECT au.orcid, a.work_id, w.type, w.is_retracted, w.year FROM authorships a JOIN authors au USING(author_id) JOIN works w USING(work_id) WHERE au.orcid IN ('0000-0002-9841-6229','0000-0001-6105-5008','0000-0009-2298-3695','0000-0000-8073-7028'))
SELECT orcid, count(DISTINCT work_id) allw, count(DISTINCT work_id) FILTER (WHERE type IN ('ART','REV') AND is_retracted='false') rs, string_agg(DISTINCT work_id||':'||type||':'||is_retracted, ',') FILTER (WHERE NOT (type IN ('ART','REV') AND is_retracted='false')) nonrs FROM p GROUP BY 1;
```
```sql
SELECT 'ellison_all' k, string_agg(DISTINCT a.work_id, ',') v FROM authorships a JOIN authors au USING(author_id) WHERE au.orcid='0000-0002-9841-6229';
```
```sql
SELECT au.orcid, count(DISTINCT a.work_id) n FROM rw r JOIN authorships a USING(work_id) JOIN anc x ON x.id=a.institution_id AND x.anc='I02' JOIN authors au USING(author_id) GROUP BY 1 ORDER BY 2 DESC LIMIT 4;
```
```sql
SELECT 'S02_2024' k, count(*) FROM rw WHERE source_id='S02' AND year='2024' UNION ALL SELECT 'S01', count(*) FROM rw WHERE source_id='S01' UNION ALL SELECT 'S06', count(*) FROM rw WHERE source_id='S06' UNION ALL SELECT 'S04', count(*) FROM rw WHERE source_id='S04'
UNION ALL SELECT 'albury_S01', count(DISTINCT r.work_id) FROM rw r JOIN authorships a USING(work_id) JOIN anc x ON x.id=a.institution_id AND x.anc='I02' WHERE r.source_id='S01'
UNION ALL SELECT 'albury_S01_S02_S06_S07_S09_S11', 0;
```
```sql
WITH v AS (SELECT work_id, work_id base FROM works UNION SELECT work_id, version_of FROM works WHERE version_of<>''), vw AS (SELECT r.work_id FROM rw r JOIN authorships a USING(work_id) JOIN authors au USING(author_id) WHERE au.orcid='0000-0000-8073-7028')
SELECT count(DISTINCT w.work_id) works, count(c.citing_work_id) cites_rows, count(DISTINCT (c.citing_work_id, w.work_id)) cites FROM (SELECT DISTINCT work_id FROM vw) w LEFT JOIN works wk ON wk.work_id=w.work_id LEFT JOIN citations c ON c.cited_work_id=w.work_id OR c.cited_work_id IN (SELECT work_id FROM works WHERE version_of=w.work_id);
```
```sql
WITH k AS (SELECT DISTINCT r.work_id FROM rw r JOIN authorships a USING(work_id) JOIN anc x ON x.id=a.institution_id AND x.anc='I07'),
cc AS (SELECT DISTINCT k.work_id, c.citing_work_id FROM k JOIN works w ON w.work_id=k.work_id JOIN citations c ON c.cited_work_id=k.work_id OR c.cited_work_id IN (SELECT work_id FROM works WHERE version_of=k.work_id))
SELECT 'top' t, work_id, count(*) n FROM cc GROUP BY 2 ORDER BY 3 DESC LIMIT 3;
```
```sql
WITH k AS (SELECT DISTINCT r.work_id FROM rw r JOIN authorships a USING(work_id) JOIN anc x ON x.id=a.institution_id AND x.anc='I07'),
cc AS (SELECT DISTINCT c.citing_work_id FROM citations c WHERE c.cited_work_id IN (SELECT work_id FROM k) OR c.cited_work_id IN (SELECT work_id FROM works WHERE version_of IN (SELECT work_id FROM k)))
SELECT x.anc, count(DISTINCT cc.citing_work_id) n FROM cc JOIN authorships a ON a.work_id=cc.citing_work_id JOIN anc x ON x.id=a.institution_id WHERE x.anc<>'I07' AND x.anc NOT IN (SELECT id FROM anc WHERE anc='I07') AND x.anc IN (SELECT institution_id FROM institutions WHERE parent_id IS NULL OR parent_id='' OR type='AFF') GROUP BY 1 ORDER BY 2 DESC LIMIT 5;
```
```sql
WITH k AS (SELECT DISTINCT r.work_id FROM rw r JOIN authorships a USING(work_id) JOIN anc x ON x.id=a.institution_id AND x.anc='I07'),
cc AS (SELECT DISTINCT c.citing_work_id FROM citations c WHERE c.cited_work_id IN (SELECT work_id FROM k) OR c.cited_work_id IN (SELECT work_id FROM works WHERE version_of IN (SELECT work_id FROM k)))
SELECT count(DISTINCT cc.citing_work_id) FROM cc JOIN authorships a ON a.work_id=cc.citing_work_id JOIN anc x ON x.id=a.institution_id AND x.anc='I09';
```
```sql
WITH l1 AS (SELECT DISTINCT citing_work_id w FROM citations WHERE cited_work_id='W01'), l2 AS (SELECT DISTINCT c.citing_work_id w FROM citations c WHERE c.cited_work_id IN (SELECT w FROM l1))
SELECT (SELECT count(*) FROM l1) n1, (SELECT count(*) FROM l2) n2, (SELECT count(*) FROM (SELECT w FROM l1 UNION SELECT w FROM l2)) both_, (SELECT string_agg(w,',') FROM l2) l2list, (SELECT string_agg(w,',') FROM l1) l1list;
```
```sql
WITH cit AS (SELECT DISTINCT citing_work_id w FROM citations WHERE cited_work_id IN ('W02','W03')),
cited AS (SELECT a.work_id, au.orcid, a.institution_id FROM authorships a JOIN authors au USING(author_id) WHERE a.work_id IN ('W02','W03')),
cg AS (SELECT a.work_id, au.orcid, a.institution_id FROM authorships a JOIN authors au USING(author_id) WHERE a.work_id IN (SELECT w FROM cit))
SELECT (SELECT count(*) FROM cit) total,
 (SELECT count(DISTINCT work_id) FROM cg WHERE orcid IN (SELECT orcid FROM cited)) author_self,
 (SELECT count(DISTINCT g.work_id) FROM cg g JOIN anc x ON x.id=g.institution_id WHERE x.anc='I02' ) FILTER_dummy_inst_citing_I02,
 (SELECT count(DISTINCT g.work_id) FROM cg g JOIN anc x ON x.id=g.institution_id WHERE x.anc='I01') citing_I01,
 (SELECT count(DISTINCT g.work_id) FROM cg g JOIN institutions i ON i.institution_id=g.institution_id WHERE i.country IN (SELECT i2.country FROM cited d JOIN institutions i2 ON i2.institution_id=d.institution_id)) country_self;
```

Query results

Query 1:

| orcid | allw | rs | nonrs |
|---|---|---|---|
| 0000-0009-2298-3695 | 18 | 15 | W219:PPR:false,W02:PPR:false,W05:ERR:false |
| 0000-0001-6105-5008 | 10 | 9 | W556:PPR:false |
| 0000-0002-9841-6229 | 32 | 22 | W05:ERR:false,W04:EDT:false,W374:ART:true,W936:EDT:false,W949:ERR:false,W02:PPR:false,W663:PPR:false,W612:PPR:false,W813:ERR:false,W271:EDT:false |
| 0000-0000-8073-7028 | 18 | 15 | W693:ART:true,W23:ART:true,W1025:ART:true |

Query 2:

| k | v |
|---|---|
| ellison_all | W02,W271,W663,W676,W763,W19,W04,W06,W555,W813,W905,W1048,W03,W1050,W12,W01,W817,W984,W05,W07,W464,W869,W374,W612,W936,W206,W843,W949,W972,W238,W449,W1005 |

Query 3:

| orcid | n |
|---|---|
| 0000-0002-9841-6229 | 22 |
| 0000-0009-2298-3695 | 15 |
| 0000-0000-4633-9010 | 14 |
| 0000-0003-7597-9990 | 14 |

Query 4:

| k | count_star() |
|---|---|
| S02_2024 | 25 |
| S01 | 95 |
| S06 | 26 |
| S04 | 13 |
| albury_S01 | 19 |
| albury_S01_S02_S06_S07_S09_S11 | 0 |

Query 5 failed: Not implemented Error: Cannot perform non-inner join on subquery!

Query 6:

| t | work_id | n |
|---|---|---|
| top | W22 | 19 |
| top | W20 | 14 |
| top | W21 | 3 |

Query 7:

| anc | n |
|---|---|
| I12 | 13 |
| I01 | 5 |
| I11 | 4 |
| I09 | 4 |
| I14 | 3 |

Query 8:

| count(DISTINCT cc.citing_work_id) |
|---|
| 4 |

Query 9:

| n1 | n2 | both_ | l2list | l1list |
|---|---|---|---|---|
| 8 | 14 | 20 | W801,W844,W1042,W396,W682,W90,W922,W337,W546,W51,W50,W52,W895,W338 | W40,W844,W602,W657,W41,W895,W229,W660 |

Query 10:

| total | author_self | FILTER_dummy_inst_citing_I02 | citing_I01 | country_self |
|---|---|---|---|---|
| 30 | 7 | 11 | 16 | 21 |

Run more queries, or give the final answers.

The model

```sql
WITH vw AS (SELECT DISTINCT r.work_id FROM rw r JOIN authorships a USING(work_id) JOIN authors au USING(author_id) WHERE au.orcid='0000-0000-8073-7028')
SELECT count(*) rows_, count(DISTINCT c.citing_work_id||c.cited_work_id) d FROM citations c WHERE c.cited_work_id IN (SELECT work_id FROM vw) OR c.cited_work_id IN (SELECT work_id FROM works WHERE version_of IN (SELECT work_id FROM vw));
```
```sql
SELECT s.tier, count(DISTINCT r.work_id) n FROM rw r JOIN sources s USING(source_id) JOIN authorships a USING(work_id) JOIN anc x ON x.id=a.institution_id AND x.anc='I02' WHERE r.record_source<>'STG' GROUP BY 1 ORDER BY 1;
```

Query results

Query 1:

| rows_ | d |
|---|---|
| 41 | 41 |

Query 2:

| tier | n |
|---|---|
| K0 | 6 |
| K1 | 29 |
| K2 | 52 |
| K3 | 19 |

Run more queries, or give the final answers.

The model

**Q1.** 1. A01, A02 and A03 all carry ORCID 0000-0002-9841-6229, so they are one person. The other "M. Ellison" records (A04, A102) have different ORCIDs.
**Q2.** 32 works. These are every work linked to that ORCID (A01, A02, A03, A101), including the editorials, errata and preprints: W01, W02, W03, W04, W05, W06, W07, W12, W19, W206, W238, W271, W374, W449, W464, W555, W612, W663, W676, W763, W813, W817, W843, W869, W905, W936, W949, W972, W984, W1005, W1048, W1050.
**Q3.** No. W37 is by A05 (ORCID …6105-5008, Albury Metropolitan) and W38 by A06 (ORCID …5475-1779, Lorvain), so they are different people.
**Q4.** 9. A05 and A104 share an ORCID, and nine of their ten works are research works. The tenth, W556, is a preprint.
**Q5.** No. W08's M. Ellison is A04 (ORCID …8662-8360, Kesting), which differs from Mara Ellison's ORCID.
**Q6.** Mara Ellison (A01), with 22 research works credited to I02 or its units.
**Q7.** 22. Of her 32 works, the other 10 are 3 editorials, 3 errata, 3 preprints and the retracted W374.
**Q8.** I01. I04 sits under I03, which sits under I02, which sits under I01.
**Q9.** 106. This counts research works (ART or REV, not retracted) and includes I03, I04 and I19. I did not roll up the affiliate I06.
**Q10.** 155, counting units I02–I05, I19 and I20 but not the affiliates.
**Q11.** 58. I excluded the 19 record-source "STG" records from Kesting's 77, because I treated them as staging records; the data does not define STG, so this is an assumption.
**Q12.** Full counting gives 13 and fractional counting gives 11.0.
**Q13.** On full counting Rostval (I09) has more, 16 against 13. On fractional counting Kesting (I07) has more, 11.0 against 4.0.
**Q14.** Full counting gives 47 and fractional counting gives 43.0.
**Q15.** 25.
**Q16.** 89. This is 95 minus 6 STG records.
**Q17.** W02, W663, W612 (preprints), W04, W271, W936 (editorials) and W05, W813, W949 (errata). W374 is excluded from this list because it is retracted, not because of its type.
**Q18.** 15. Priya Ramanathan has 18 works, and three of them (W219, W02, W05) are preprints or an erratum.
**Q19.** 30 distinct works cite W02 or W03.
**Q20.** The 23 preprints whose original is also in the data: W02, W42, W210, W219, W226, W263, W322, W407, W416, W432, W460, W506, W522, W524, W545, W556, W611, W612, W663, W728, W730, W877, W1049.
**Q21.** 46.
**Q22.** W30 has more in total (18 against 13 for W31 plus its preprint W42). Within the publication year and the two years after, W31 has more (13 against 2).
**Q23.** W30 has 18 citations in total and 2 within 2019–2021.
**Q24.** 15. Three of her 18 works are retracted (W693, W23, W1025).
**Q25.** 41, counting citations to both versions.
**Q26.** No. W23 is retracted, so it does not count as research output.
**Q27.** I12 (Université de Lorvain), with 13 citing works.
**Q28.** 4.
**Q29.** Self-citations at author level: 7, at University of Albury level: 11, and at country level: 21, out of 30 citing works.
**Q30.** 16.
**Q31.** The 14 works that cite a work citing W01: W801, W844, W1042, W396, W682, W90, W922, W337, W546, W51, W50, W52, W895, W338.
**Q32.** 20 distinct works are within two hops of W01: 8 citing it directly and 14 at the second hop, with W844 and W895 in both.
**Q33.** W22, with 19 citing works.
**Q34.** 6. The other 19 Kesting 2025 records are STG, excluded on the same assumption as Q11.
**Q35.** 24.
**Q36.** 26.
**Q37.** I04, the Tessel Laboratory, which is the first-listed affiliation.
**Q38.** I06 and I04, plus I04's parents I03, I02 and I01.
**Q39.** 19, taking K3 as the highest tier (see Q40) and excluding STG records.
**Q40.** S01, on the assumption that K3 is highest. The data does not state the tier order, and K0 would instead point to S04 and S13.
**Q41.** No, on the same assumption: S03 is K1 and S02 is K2.
**Q42.** 13.
**Q43.** Yes. W07 is an article, not retracted, from the FEED source.
**Q44.** I02, I03, I04, I05, I19 and I20. I excluded the affiliates I06 and I21.
**Q45.** 22.
**Q46.** I01, via I02. I06 is typed as an affiliate (AFF), so it is not a full member of the system.
**Q47.** Under full counting each work counts once per institution, so Rostval's 16 works beat Kesting's 13. Under fractional counting Kesting's works are mostly credited to its own authors (11.0), while Rostval's are shared with other institutions (4.0).
**Q48.** The premise looks wrong. I04 sits under I03, which sits under I02, so W70 should count for the University of Albury.

```json
{"Q1":{"count":1},
"Q2":{"works":["W01","W02","W03","W04","W05","W06","W07","W12","W19","W206","W238","W271","W374","W449","W464","W555","W612","W663","W676","W763","W813","W817","W843","W869","W905","W936","W949","W972","W984","W1005","W1048","W1050"]},
"Q3":{"same":"no"},
"Q4":{"count":9},
"Q5":{"same":"no"},
"Q6":{"author":"A01"},
"Q7":{"count":22},
"Q8":{"parent":"I01"},
"Q9":{"count":106},
"Q10":{"count":155},
"Q11":{"count":58},
"Q12":{"full":13,"fractional":11},
"Q13":{"full":"I09","fractional":"I07"},
"Q14":{"full":47,"fractional":43},
"Q15":{"count":25},
"Q16":{"count":89},
"Q17":{"works":["W02","W04","W05","W271","W612","W663","W813","W936","W949"]},
"Q18":{"count":15},
"Q19":{"count":30},
"Q20":{"works":["W02","W42","W210","W219","W226","W263","W322","W407","W416","W432","W460","W506","W522","W524","W545","W556","W611","W612","W663","W728","W730","W877","W1049"]},
"Q21":{"count":46},
"Q22":{"more_in_total":"W30","more_in_window":"W31"},
"Q23":{"total":18,"in_window":2},
"Q24":{"count":15},
"Q25":{"count":41},
"Q26":{"counts":"no"},
"Q27":{"institution":"I12","citing_works":13},
"Q28":{"count":4},
"Q29":{"author":7,"institution":11,"country":21},
"Q30":{"count":16},
"Q31":{"works":["W801","W844","W1042","W396","W682","W90","W922","W337","W546","W51","W50","W52","W895","W338"]},
"Q32":{"count":20},
"Q33":{"work":"W22"},
"Q34":{"count":6},
"Q35":{"count":24},
"Q36":{"count":26},
"Q37":{"institution":"I04"},
"Q38":{"institutions":["I06","I04","I03","I02","I01"]},
"Q39":{"count":19},
"Q40":{"source":"S01"},
"Q41":{"higher":"no"},
"Q42":{"count":13},
"Q43":{"research":"yes"},
"Q44":{"institutions":["I02","I03","I04","I05","I19","I20"]},
"Q45":{"count":22},
"Q46":{"parent":"I01"}}
```