D3 sql+executed, run 2: the query session

26 queries0 failed4 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.

- `works` (802 rows): work_id, title, year, type, source_id, retracted, research_output, record_ids
- `people` (160 rows): person_id, name, orcid, author_ids
- `credits` (1430 rows): work_id, person_id, institution_id
- `institutions` (31 rows): institution_id, name, ror, country, type, parent_id
- `institution_units` (44 rows): institution_id, counts_towards, levels_up
- `sources` (14 rows): source_id, name, issn, type, tier, tier_rank, delisted
- `citations` (979 rows): citing_work_id, cited_work_id

These tables are the data after its rules have been applied. Test records are gone. A preprint is merged into its published version (record_ids lists the records each work merges). research_output says whether a work counts as research output. Author records are merged into people (author_ids lists them). credits gives the one institution credited for each person on each work. institution_units lists every institution each institution's works count towards, itself included, with how many levels up. citations links merged works, and only from citing works that count. tier_rank ranks journal tiers, 1 the highest; a delisted source's works are not research output.

## 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 tables.

```sql
SELECT * FROM institutions ORDER BY institution_id;
```
```sql
SELECT * FROM institution_units ORDER BY institution_id, levels_up;
```
```sql
SELECT * FROM sources ORDER BY source_id;
```
```sql
SELECT * FROM people ORDER BY person_id LIMIT 200;
```

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:

| institution_id | counts_towards | levels_up |
|---|---|---|
| I01 | I01 | 0 |
| I02 | I02 | 0 |
| I02 | I01 | 1 |
| I03 | I03 | 0 |
| I03 | I02 | 1 |
| I03 | I01 | 2 |
| I04 | I04 | 0 |
| I04 | I03 | 1 |
| I04 | I02 | 2 |
| I04 | I01 | 3 |
| I05 | I05 | 0 |
| I05 | I01 | 1 |
| I06 | I06 | 0 |
| I07 | I07 | 0 |
| I08 | I08 | 0 |
| I08 | I07 | 1 |
| I09 | I09 | 0 |
| I10 | I10 | 0 |
| I11 | I11 | 0 |
| I12 | I12 | 0 |
| I13 | I13 | 0 |
| I14 | I14 | 0 |
| I15 | I15 | 0 |
| I16 | I16 | 0 |
| I17 | I17 | 0 |
| I18 | I18 | 0 |
| I19 | I19 | 0 |
| I19 | I02 | 1 |
| I19 | I01 | 2 |
| I20 | I20 | 0 |
| I20 | I05 | 1 |
| I20 | I01 | 2 |
| I21 | I21 | 0 |
| I22 | I22 | 0 |
| I23 | I23 | 0 |
| I23 | I09 | 1 |
| I24 | I24 | 0 |
| I25 | I25 | 0 |
| I26 | I26 | 0 |
| I27 | I27 | 0 |
| I28 | I28 | 0 |
| I29 | I29 | 0 |
| I30 | I30 | 0 |
| I31 | I31 | 0 |

Query 3:

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

Query 4:

| person_id | name | orcid | author_ids |
|---|---|---|---|
| A01 | Mara Ellison | 0000-0002-9841-6229 | A01 A02 A03 A101 |
| A04 | M. Ellison | 0000-0004-8662-8360 | A04 |
| A05 | Jonas Brandt | 0000-0001-6105-5008 | A05 A104 |
| A06 | Jonas Brandt | 0000-0007-5475-1779 | A06 |
| A07 | Priya Ramanathan | 0000-0009-2298-3695 | A07 |
| A08 | Tomasz Wierzba | 0000-0007-9318-6032 | A08 A106 |
| A09 | Lena Vossberg | 0000-0000-8073-7028 | A09 A103 |
| A10 | Dirk Hallmann | 0000-0003-8222-6468 | A10 |
| A102 | M. Ellison | 0000-0008-4738-8328 | A102 |
| A105 | Jonas Brandt | 0000-0004-9046-7961 | A105 |
| A107 | S. de Raat | 0000-0006-1642-5247 | A107 A11 |
| A108 | Priya Ramanathan | 0000-0007-1802-8393 | A108 |
| A109 | Greta Yelland | 0000-0002-6545-4969 | A109 |
| A110 | Quinn Yelland | 0000-0003-7597-9990 | A110 |
| A111 | Hester Falconer | 0000-0007-5409-8704 | A111 |
| A112 | Katrin Elphick | 0000-0008-6293-8407 | A112 |
| A113 | Henrik Corbridge | 0000-0001-7569-4541 | A113 |
| A114 | Ivo Brockmann | 0000-0009-9653-9901 | A114 |
| A115 | Tamsin Vandersloot | 0000-0008-5622-7508 | A115 |
| A116 | Emrys Bellamore | 0000-0001-3732-2664 | A116 |
| A117 | Viggo Bellamore | 0000-0002-5926-5216 | A117 |
| A118 | Dorian Bellamore | 0000-0000-4633-9010 | A118 |
| A119 | Jarno Quarrie | 0000-0009-8605-0541 | A119 |
| A12 | Pieter Maas | 0000-0007-6472-5877 | A12 |
| A120 | Linus Haverkamp | 0000-0000-3133-9422 | A120 |
| A121 | Marit Sallis | 0000-0001-8432-2588 | A121 |
| A122 | Katrin Dannecker | 0000-0005-9430-8644 | A122 |
| A123 | Florin Penhallow | 0000-0008-6186-9535 | A123 |
| A124 | Niko Vandersloot | 0000-0008-3786-7418 | A124 |
| A125 | Greta Ostergaard | 0000-0002-5410-0810 | A125 |
| A126 | Yara Quarrie | 0000-0002-3972-8364 | A126 |
| A127 | Delphine Ashdown | 0000-0000-5148-3260 | A127 |
| A128 | Jarno Tregunna |  | A128 |
| A129 | Delphine Ibbotson | 0000-0000-6389-2468 | A129 |
| A13 | Claire Fontanet | 0000-0001-8841-3301 | A13 |
| A130 | Tamsin Castellan | 0000-0004-8526-1242 | A130 |
| A131 | Emrys Nordahl | 0000-0000-3843-6782 | A131 |
| A132 | Linus Penhallow | 0000-0000-9780-0868 | A132 |
| A133 | Liesel Lachance | 0000-0003-1768-1087 | A133 |
| A134 | Silas Castellan | 0000-0003-3481-4429 | A134 |
| A135 | Olwen Quarrie | 0000-0001-4750-0051 | A135 |
| A136 | Jolien Haverkamp | 0000-0009-6519-3014 | A136 |
| A137 | Olwen Uttley | 0000-0006-0842-6639 | A137 |
| A138 | Olwen Whitcombe | 0000-0008-9432-6799 | A138 |
| A139 | Odile Tregunna | 0000-0002-2495-1719 | A139 |
| A14 | Hugo Marchetti | 0000-0007-5682-0525 | A14 |
| A140 | Jolien Bellamore | 0000-0009-0548-3869 | A140 |
| A141 | Delphine Bellamore | 0000-0003-0209-4519 | A141 |
| A142 | Greta Rademaker | 0000-0009-9961-6216 | A142 |
| A143 | Marit Ibbotson | 0000-0009-8383-5608 | A143 |
| A144 | Silas Ashdown | 0000-0007-8521-8973 | A144 |
| A145 | Ivo Corbridge | 0000-0006-1367-0053 | A145 |
| A146 | Cosimo Bellamore | 0000-0002-3308-6027 | A146 |
| A147 | Niko Uttley | 0000-0009-7757-5966 | A147 |
| A148 | Kester Aldous | 0000-0004-3203-0789 | A148 |
| A149 | Liesel Whitcombe | 0000-0008-7432-0373 | A149 |
| A15 | Aisha Okonjo | 0000-0008-9831-9489 | A15 |
| A150 | Yara Castellan | 0000-0006-4842-1959 | A150 |
| A151 | Elsa Penhallow | 0000-0008-1093-6341 | A151 |
| A152 | Florin Whitcombe |  | A152 |
(100 more rows not shown)

Run more queries, or give the final answers.

The model

<invoke name="sql">
</invoke>
```sql
SELECT * FROM works WHERE work_id IN ('W01','W07','W08','W23','W30','W31','W37','W38','W70','W71') OR title ILIKE '%Drift-aware%' ORDER BY work_id;
```
```sql
SELECT w.work_id, w.title, w.year, w.type, w.source_id, w.retracted, w.research_output, w.record_ids, string_agg(c.person_id||'@'||c.institution_id, ', ') cr FROM works w LEFT JOIN credits c USING(work_id) WHERE w.work_id IN (SELECT work_id FROM credits WHERE person_id IN ('A01','A04','A102','A05','A06','A105','A09','A07','A108')) GROUP BY ALL ORDER BY 1;
```
```sql
SELECT type, research_output, count(*) FROM works GROUP BY ALL ORDER BY 1,2;
```

Query results

Query 1:

| work_id | title | year | type | source_id | retracted | research_output | record_ids |
|---|---|---|---|---|---|---|---|
| W01 | Adaptive path planning for underwater gliders | 2021 | ART | S01 | false | yes | W01 |
| W03 | Drift-aware mapping with low-cost sonar | 2023 | ART | S02 | false | yes | W02 W03 |
| W05 | Erratum: Drift-aware mapping with low-cost sonar | 2024 | ERR | S02 | false | no | W05 |
| W07 | Low-cost sonar arrays for coastal monitoring | 2020 | ART | S04 | false | no | W07 |
| W08 | Turbulent wake modelling behind tidal turbines | 2025 | ART | S01 | false | yes | W08 |
| W23 | Anomalous dissipation in estuarine plumes | 2023 | ART | S02 | true | no | W23 |
| W30 | Tidal stream resource maps for the Dunmore coast | 2019 | ART | S03 | false | yes | W30 |
| W31 | Machine-learned tidal forecasts | 2023 | ART | S01 | false | yes | W31 W42 |
| W37 | Port traffic and seabed disturbance | 2022 | ART | S03 | false | yes | W37 |
| W38 | Seagrass mapping from aerial imagery | 2023 | ART | S02 | false | yes | W38 |
| W70 | Optical backscatter in turbid estuaries | 2023 | ART | S02 | false | yes | W70 |
| W71 | Low-power optical sensors for gliders | 2024 | ART | S01 | false | yes | W71 |

Query 2:

| work_id | title | year | type | source_id | retracted | research_output | record_ids | cr |
|---|---|---|---|---|---|---|---|---|
| W01 | Adaptive path planning for underwater gliders | 2021 | ART | S01 | false | yes | W01 | A07@I03, A01@I02 |
| W03 | Drift-aware mapping with low-cost sonar | 2023 | ART | S02 | false | yes | W02 W03 | A07@I03, A01@I02 |
| W04 | Editorial: a new decade of marine autonomy | 2023 | EDT | S01 | false | no | W04 | A01@I02 |
| W05 | Erratum: Drift-aware mapping with low-cost sonar | 2024 | ERR | S02 | false | no | W05 | A07@I03, A01@I02 |
| W06 | Shoreline change detection from glider transects | 2024 | ART | S03 | false | yes | W06 | A15@I13, A01@I02 |
| W07 | Low-cost sonar arrays for coastal monitoring | 2020 | ART | S04 | false | no | W07 | A01@I02 |
| W08 | Turbulent wake modelling behind tidal turbines | 2025 | ART | S01 | false | yes | W08 | A04@I07 |
| W1005 | Remote sensing of buoy telemetry in fjords | 2025 | ART | S02 | false | yes | W1005 | A198@I17, A111@I02, A01@I02 |
| W1018 | Remote sensing of estuarine salinity in the Brevin shelf | 2025 | ART | S08 | false | yes | W1018 | A108@I16 |
| W1020 | Machine learning for vortex shedding in mixed-energy coasts | 2020 | ART | S13 | false | no | W1020 | A04@I07 |
| W1025 | Long-term trends in stratified flows | 2020 | ART | S02 | true | no | W1025 | A09@I07 |
| W1031 | Remote sensing of internal waves in the open shelf | 2025 | ART | S01 | false | yes | W1031 | A158@I12, A06@I12 |
| W1038 | Low-cost methods for marsh accretion | 2020 | ART | S02 | false | yes | W1038 W556 | A164@I14, A05@I05 |
| W1048 | Long-term trends in fleet coordination in shallow channels | 2023 | ART | S10 | false | yes | W1048 | A01@I02 |
| W1050 | Low-cost methods for glider navigation in the Wexcombe marshes | 2024 | ART | S09 | false | yes | W1050 | A114@I02, A01@I02 |
| W1056 | A model of sonar imaging | 2023 | ART | S02 | false | yes | W1056 | A102@I09 |
| W12 | Sonar mosaics from glider fleets | 2024 | ART | S02 | false | yes | W12 | A16@I05, A01@I02 |
| W13 | Fleet coordination for survey gliders | 2024 | ART | S01 | false | yes | W13 | A07@I03 |
| W14 | Harbour mapping with autonomous surface vessels | 2025 | ART | S03 | false | yes | W14 | A05@I05 |
| W16 | Sonar-based wake detection | 2025 | ART | S02 | false | yes | W16 | A04@I07 |
| W19 | Glider-borne turbidity sensing | 2025 | ART | S02 | false | yes | W19 | A01@I02 |
| W20 | Bed shear stress under breaking waves | 2024 | ART | S01 | false | yes | W20 | A13@I11, A09@I07 |
| W203 | A community intercomparison of beach profiles | 2024 | ART | S03 | false | yes | W203 | A225@I28, A144@I08, A143@I08, A139@I07, A10@I08, A09@I07 |
| W206 | Observations of buoy telemetry in a semi-enclosed sea | 2020 | ART | S01 | false | yes | W206 | A123@I19, A01@I02 |
| W21 | A field campaign on tidal mixing | 2024 | ART | S02 | false | yes | W21 | A12@I10, A107@I09, A10@I08, A09@I07 |
| W22 | Turbulence closures for coastal models: a review | 2024 | REV | S03 | false | yes | W22 | A09@I07 |
| W23 | Anomalous dissipation in estuarine plumes | 2023 | ART | S02 | true | no | W23 | A09@I07 |
| W232 | A model of channel hydraulics | 2019 | ART | S06 | false | yes | W232 | A137@I07, A09@I07 |
| W238 | Observations of survey vessel autonomy in tidal inlets | 2023 | ART | S02 | false | yes | W238 | A32@I17, A111@I02, A01@I02 |
| W242 | Remote sensing of estuarine salinity | 2024 | ART | S03 | false | yes | W242 | A105@I14 |
| W251 | Remote sensing of sensor calibration | 2023 | ART | S02 | false | yes | W219 W251 | A125@I19, A07@I03 |
| W262 | Revisiting sensor calibration in a semi-enclosed sea | 2025 | ART | S01 | false | yes | W262 | A194@I30, A07@I03 |
| W271 | Editorial: battery endurance | 2019 | EDT | S01 | false | no | W271 | A01@I02 |
| W28 | Calibration drift in conductivity sensors | 2024 | ART | S02 | false | yes | W28 | A19@I09, A16@I05, A15@I13, A06@I12 |
| W292 | A model of underwater docking in the Albury estuary | 2020 | ART | S01 | false | yes | W292 | A155@I12, A07@I03 |
| W293 | Low-cost methods for buoy telemetry | 2023 | ART | S02 | false | yes | W293 | A125@I19, A07@I03 |
| W34 | Evaluating turbulence closures against flume data | 2025 | ART | S03 | false | yes | W34 | A06@I12 |
| W357 | Observations of tidal mixing | 2025 | ART | S10 | false | yes | W357 | A20@I09, A148@I09, A107@I09, A102@I09 |
| W366 | Scaling laws for tidal mixing in harbour basins | 2022 | ART | S03 | false | yes | W366 | A219@I30, A09@I07 |
| W37 | Port traffic and seabed disturbance | 2022 | ART | S03 | false | yes | W37 | A05@I05 |
| W374 | Machine learning for underwater docking in the open shelf | 2023 | ART | S02 | true | no | W374 | A114@I02, A01@I02 |
| W38 | Seagrass mapping from aerial imagery | 2023 | ART | S02 | false | yes | W38 | A06@I12 |
| W384 | Machine learning for storm surge | 2025 | ART | S10 | false | yes | W384 | A157@I12, A06@I12 |
| W417 | A model of turbulent wakes in harbour basins | 2023 | ART | S02 | false | yes | W417 | A143@I08, A09@I07 |
| W422 | Remote sensing of coastal upwelling | 2023 | ART | S01 | false | yes | W422 W506 | A152@I09, A102@I09 |
| W428 | Revisiting turbulent wakes in the open shelf | 2022 | ART | S03 | false | yes | W428 | A174@I17, A04@I07 |
| W435 | Remote sensing of underwater docking in a macrotidal bay | 2023 | ART | S06 | false | yes | W435 | A125@I19, A07@I03 |
| W437 | Editorial: low-cost methods for beach profiles in shallow channels | 2024 | EDT | S08 | false | no | W437 | A176@I24, A108@I16 |
| W449 | Machine learning for glider navigation | 2022 | ART | S04 | false | no | W449 | A01@I02 |
| W464 | A framework for turbidity sensing | 2021 | ART | S09 | false | yes | W464 W663 | A224@I16, A01@I02 |
| W478 | Long-term trends in bed shear stress in a semi-enclosed sea | 2019 | ART | S01 | false | yes | W478 | A09@I07 |
| W487 | Remote sensing of wave runup in the open shelf | 2024 | ART | S11 | false | yes | W487 | A221@I30, A195@I31, A177@I25, A102@I09 |
| W509 | Uncertainty in harbour seiches estimates in fjords | 2024 | ART | S08 | false | yes | W509 | A198@I17, A183@I31, A108@I16 |
| W525 | A model of harbour seiches in the open shelf | 2024 | ART | S02 | false | yes | W525 | A158@I12, A06@I12 |
| W531 | A framework for stratified flows in shallow channels | 2023 | ART | S01 | false | yes | W531 | A09@I07 |
| W553 | Field measurements of buoy telemetry in mixed-energy coasts | 2022 | ART | S03 | false | yes | W553 | A07@I03 |
| W554 | Remote sensing of coastal upwelling in a macrotidal bay | 2024 | ART | S01 | false | yes | W554 | A05@I05 |
| W555 | A model of underwater docking in a macrotidal bay | 2020 | ART | S09 | false | yes | W555 | A123@I19, A01@I02 |
| W565 | Observations of beach profiles | 2019 | ART | S03 | false | yes | W565 | A05@I05 |
| W598 | Revisiting underwater docking in a laboratory flume | 2024 | ART | S10 | false | yes | W598 | A07@I03 |
(41 more rows not shown)

Query 3:

| type | research_output | count_star() |
|---|---|---|
| ART | no | 33 |
| ART | yes | 620 |
| EDT | no | 33 |
| ERR | no | 8 |
| PPR | yes | 11 |
| PTX | no | 12 |
| REV | no | 4 |
| REV | yes | 69 |
| SUP | yes | 12 |

Run more queries, or give the final answers.

The model

<invoke name="sql">
</invoke>
```sql
SELECT 'Q2' q, string_agg(work_id||':'||type||':'||research_output, ', ' ORDER BY work_id) v FROM (SELECT DISTINCT w.work_id,type,research_output FROM works w JOIN credits c USING(work_id) WHERE person_id='A01');
```
```sql
WITH rw AS (SELECT * FROM works WHERE research_output='yes')
SELECT p, count(DISTINCT work_id) n FROM (SELECT c.person_id p, rw.work_id FROM credits c JOIN rw USING(work_id) WHERE person_id IN ('A01','A05','A07','A09','A108','A06','A105')) GROUP BY p ORDER BY p;
```
```sql
WITH rw AS (SELECT * FROM works WHERE research_output='yes')
SELECT c.person_id, count(DISTINCT c.work_id) n FROM credits c JOIN rw USING(work_id) JOIN institution_units u ON u.institution_id=c.institution_id AND u.counts_towards='I02' GROUP BY 1 ORDER BY 2 DESC LIMIT 5;
```
```sql
WITH rw AS (SELECT * FROM works WHERE research_output='yes')
SELECT u.counts_towards inst, count(DISTINCT rw.work_id) n, count(DISTINCT CASE WHEN year='2024' THEN rw.work_id END) n24, count(DISTINCT CASE WHEN year='2025' THEN rw.work_id END) n25 FROM credits c JOIN rw USING(work_id) JOIN institution_units u USING(institution_id) WHERE u.counts_towards IN ('I01','I02','I06','I07','I08','I09','I14') GROUP BY 1 ORDER BY 1;
```
```sql
WITH rw AS (SELECT * FROM works WHERE research_output='yes' AND year='2024'),
n AS (SELECT work_id, count(*) k FROM credits GROUP BY 1),
x AS (SELECT u.counts_towards inst, c.work_id, count(*) m, any_value(k) k FROM credits c JOIN rw USING(work_id) JOIN n USING(work_id) JOIN institution_units u USING(institution_id) WHERE u.counts_towards IN ('I07','I09') GROUP BY 1,2)
SELECT inst, count(*) full_, sum(m*1.0/k) frac FROM x GROUP BY 1;
```
```sql
WITH rw AS (SELECT * FROM works WHERE research_output='yes' AND year='2024'),
n AS (SELECT work_id, count(*) k FROM credits GROUP BY 1)
SELECT i.country, count(DISTINCT work_id) full_, sum(1.0/k) frac FROM credits c JOIN rw USING(work_id) JOIN n USING(work_id) JOIN institutions i USING(institution_id) GROUP BY 1 HAVING i.country='GB';
```
```sql
SELECT source_id, count(*) n, count(*) FILTER (WHERE year='2024') n24 FROM works WHERE research_output='yes' AND source_id IN ('S01','S02','S04','S06') GROUP BY 1 ORDER BY 1;
```
```sql
SELECT * FROM citations WHERE cited_work_id IN ('W02','W03','W01','W30','W31','W42') OR citing_work_id IN ('W02','W03','W42') ORDER BY cited_work_id, citing_work_id;
```
```sql
SELECT w.work_id,w.title,w.year,w.type,w.research_output,w.record_ids,string_agg(c.person_id||'@'||c.institution_id,', ') cr FROM works w LEFT JOIN credits c USING(work_id) WHERE w.work_id IN ('W70','W71') OR w.type='PPR' OR w.record_ids LIKE '% %' AND w.work_id IN ('W30','W31') GROUP BY ALL ORDER BY 1;
```

Query results

Query 1:

| q | v |
|---|---|
| Q2 | W01:ART:yes, W03:ART:yes, W04:EDT:no, W05:ERR:no, W06:ART:yes, W07:ART:no, W1005:ART:yes, W1048:ART:yes, W1050:ART:yes, W12:ART:yes, W19:ART:yes, W206:ART:yes, W238:ART:yes, W271:EDT:no, W374:ART:no, W449:ART:no, W464:ART:yes, W555:ART:yes, W676:ART:yes, W763:ART:no, W813:ERR:no, W817:ART:yes, W843:ART:yes, W869:ART:yes, W905:ART:yes, W936:EDT:no, W949:ERR:no, W972:ART:yes, W984:ART:yes |

Query 2:

| p | n |
|---|---|
| A01 | 19 |
| A05 | 9 |
| A06 | 7 |
| A07 | 15 |
| A09 | 12 |
| A105 | 3 |
| A108 | 6 |

Query 3:

| person_id | n |
|---|---|
| A01 | 19 |
| A07 | 15 |
| A110 | 14 |
| A118 | 14 |
| A125 | 10 |

Query 4:

| inst | n | n24 | n25 |
|---|---|---|---|
| I01 | 140 | 34 | 16 |
| I02 | 95 | 25 | 12 |
| I06 | 16 | 2 | 3 |
| I07 | 59 | 13 | 7 |
| I08 | 25 | 6 | 3 |
| I09 | 48 | 16 | 4 |
| I14 | 45 | 16 | 6 |

Query 5:

| inst | full_ | frac |
|---|---|---|
| I07 | 13 | 11.0 |
| I09 | 16 | 4.0 |

Query 6:

| country | full_ | frac |
|---|---|---|
| GB | 46 | 41.99999999999999 |

Query 7:

| source_id | n | n24 |
|---|---|---|
| S01 | 88 | 24 |
| S02 | 110 | 25 |
| S06 | 36 | 9 |

Query 8:

| citing_work_id | cited_work_id |
|---|---|
| W229 | W01 |
| W40 | W01 |
| W41 | W01 |
| W602 | W01 |
| W657 | W01 |
| W660 | W01 |
| W895 | W01 |
| W1048 | W03 |
| W1056 | W03 |
| W12 | W03 |
| W13 | W03 |
| W14 | W03 |
| W15 | W03 |
| W16 | W03 |
| W17 | W03 |
| W18 | W03 |
| W214 | W03 |
| W273 | W03 |
| W315 | W03 |
| W349 | W03 |
| W535 | W03 |
| W544 | W03 |
| W558 | W03 |
| W598 | W03 |
| W676 | W03 |
| W711 | W03 |
| W748 | W03 |
| W761 | W03 |
| W812 | W03 |
| W833 | W03 |
| W843 | W03 |
| W889 | W03 |
| W890 | W03 |
| W931 | W03 |
| W1033 | W30 |
| W246 | W30 |
| W274 | W30 |
| W276 | W30 |
| W289 | W30 |
| W334 | W30 |
| W439 | W30 |
| W60 | W30 |
| W61 | W30 |
| W62 | W30 |
| W63 | W30 |
| W64 | W30 |
| W856 | W30 |
| W894 | W30 |
| W988 | W30 |
| W992 | W30 |
| W1039 | W31 |
| W249 | W31 |
| W318 | W31 |
| W62 | W31 |
| W65 | W31 |
| W650 | W31 |
| W66 | W31 |
| W67 | W31 |
| W757 | W31 |
| W794 | W31 |
(1 more rows not shown)

Query 9:

| work_id | title | year | type | research_output | record_ids | cr |
|---|---|---|---|---|---|---|
| W1054 | A framework for sonar imaging in the Brevin shelf | 2021 | PPR | yes | W1054 | A15@I13 |
| W18 | Comparing sonar odometry methods | 2022 | PPR | yes | W18 | A107@I09 |
| W221 | A model of marsh accretion in mixed-energy coasts | 2023 | PPR | yes | W221 | A188@I24 |
| W306 | Seasonal variability in sediment flux in the Brevin shelf | 2023 | PPR | yes | W306 | A127@I05, A130@I20 |
| W31 | Machine-learned tidal forecasts | 2023 | ART | yes | W31 W42 | A17@I14 |
| W336 | Uncertainty in river plumes estimates in a semi-enclosed sea | 2021 | PPR | yes | W336 | A198@I17 |
| W566 | A model of wave runup | 2023 | PPR | yes | W566 | A129@I05, A130@I20 |
| W70 | Optical backscatter in turbid estuaries | 2023 | ART | yes | W70 | A08@I06 |
| W71 | Low-power optical sensors for gliders | 2024 | ART | yes | W71 | A08@I04 |
| W745 | A model of sonar imaging in the Brevin shelf | 2020 | PPR | yes | W745 | A110@I02 |
| W747 | Observations of river plumes | 2024 | PPR | yes | W747 | A14@I12, A157@I12 |
| W780 | Long-term trends in ocean optics in sandy coasts | 2019 | PPR | yes | W780 | A164@I14 |
| W781 | Field measurements of turbidity sensing in the Wexcombe marshes | 2025 | PPR | yes | W781 | A161@I11, A162@I11 |
| W824 | A framework for sonar imaging | 2022 | PPR | yes | W824 | A167@I13 |

Run more queries, or give the final answers.

The model

```sql
SELECT work_id,title,year,type,source_id,retracted,research_output FROM works WHERE work_id='W763';
```
```sql
SELECT p.work_id ppr, o.work_id other, o.type, o.year, o.research_output, p.title, o.record_ids FROM works p JOIN works o ON lower(trim(o.title))=lower(trim(p.title)) AND o.work_id<>p.work_id WHERE p.type='PPR' OR o.type='PPR';
```
```sql
SELECT count(DISTINCT citing_work_id) n, count(*) rows_ FROM citations WHERE cited_work_id IN ('W02','W03');
```
```sql
SELECT c.cited_work_id, count(*) total, count(*) FILTER (WHERE cast(w.year as int) BETWEEN cast(t.year as int) AND cast(t.year as int)+2) win FROM citations c JOIN works t ON t.work_id=c.cited_work_id JOIN works w ON w.work_id=c.citing_work_id WHERE c.cited_work_id IN ('W30','W31','W42') GROUP BY 1;
```
```sql
WITH aw AS (SELECT DISTINCT w.work_id FROM works w JOIN credits c USING(work_id) WHERE c.person_id='A09' AND w.research_output='yes')
SELECT count(*) works, (SELECT count(*) FROM citations WHERE cited_work_id IN (SELECT work_id FROM aw)) cites FROM aw;
```
```sql
WITH i7 AS (SELECT DISTINCT w.work_id FROM works w JOIN credits c USING(work_id) JOIN institution_units u USING(institution_id) WHERE u.counts_towards='I07' AND w.research_output='yes'),
cit AS (SELECT DISTINCT citing_work_id FROM citations WHERE cited_work_id IN (SELECT work_id FROM i7))
SELECT u.counts_towards, count(DISTINCT cit.citing_work_id) n FROM cit JOIN credits c ON c.work_id=cit.citing_work_id JOIN institution_units u USING(institution_id) GROUP BY 1 ORDER BY 2 DESC LIMIT 6;
```
```sql
WITH tgt AS (SELECT DISTINCT citing_work_id w FROM citations WHERE cited_work_id IN ('W02','W03')),
ta AS (SELECT DISTINCT person_id FROM credits WHERE work_id IN ('W02','W03')),
ti AS (SELECT DISTINCT u.counts_towards i FROM credits c JOIN institution_units u USING(institution_id) WHERE c.work_id IN ('W02','W03')),
tc AS (SELECT DISTINCT i.country FROM credits c JOIN institutions i USING(institution_id) WHERE c.work_id IN ('W02','W03'))
SELECT count(*) citers,
 count(*) FILTER (WHERE w IN (SELECT work_id FROM credits WHERE person_id IN (SELECT person_id FROM ta))) author_self,
 count(*) FILTER (WHERE w IN (SELECT c.work_id FROM credits c JOIN institution_units u USING(institution_id) WHERE u.counts_towards='I02')) i02_self,
 count(*) FILTER (WHERE w IN (SELECT c.work_id FROM credits c JOIN institution_units u USING(institution_id) WHERE u.counts_towards='I01')) i01_self,
 count(*) FILTER (WHERE w IN (SELECT c.work_id FROM credits c JOIN institutions i USING(institution_id) WHERE i.country IN (SELECT country FROM tc))) country_self
FROM tgt;
```
```sql
WITH l1 AS (SELECT citing_work_id w FROM citations WHERE cited_work_id='W01'),
l2 AS (SELECT DISTINCT citing_work_id w FROM citations WHERE cited_work_id IN (SELECT w FROM l1))
SELECT 'Q31' q, count(*) n, string_agg(w, ',' ORDER BY w) v FROM l2
UNION ALL SELECT 'Q32', count(*), NULL FROM (SELECT w FROM l1 UNION SELECT w FROM l2);
```
```sql
WITH i7 AS (SELECT DISTINCT w.work_id FROM works w JOIN credits c USING(work_id) JOIN institution_units u USING(institution_id) WHERE u.counts_towards='I07' AND w.research_output='yes')
SELECT cited_work_id, count(*) n FROM citations WHERE cited_work_id IN (SELECT work_id FROM i7) GROUP BY 1 ORDER BY 2 DESC LIMIT 3;
```
```sql
SELECT count(DISTINCT w.work_id) FROM works w JOIN credits c USING(work_id) JOIN institution_units u USING(institution_id) WHERE u.counts_towards='I02' AND w.research_output='yes' AND w.source_id='S01';
```

Query results

Query 1:

| work_id | title | year | type | source_id | retracted | research_output |
|---|---|---|---|---|---|---|
| W763 | A model of glider navigation in a macrotidal bay | 2024 | ART | S13 | false | no |

Query 2:

(no rows)

Query 3:

| n | rows_ |
|---|---|
| 27 | 27 |

Query 4:

| cited_work_id | total | win |
|---|---|---|
| W31 | 11 | 11 |
| W30 | 16 | 2 |

Query 5:

| works | cites |
|---|---|
| 12 | 20 |

Query 6:

| counts_towards | n |
|---|---|
| I12 | 12 |
| I01 | 5 |
| I11 | 4 |
| I09 | 4 |
| I14 | 3 |
| I02 | 3 |

Query 7:

| citers | author_self | i02_self | i01_self | country_self |
|---|---|---|---|---|
| 27 | 7 | 10 | 14 | 20 |

Query 8:

| q | n | v |
|---|---|---|
| Q31 | 11 | W1042,W337,W338,W396,W50,W51,W52,W546,W682,W801,W895 |
| Q32 | 17 |  |

Query 9:

| cited_work_id | n |
|---|---|
| W20 | 13 |
| W21 | 2 |
| W417 | 2 |

Query 10:

| count(DISTINCT w.work_id) |
|---|
| 19 |

Run more queries, or give the final answers.

The model

**Q1.** 3. The names resolve to A01 (Mara Ellison, merging A01/A02/A03/A101), A04 and A102. A04 and A102 are both "M. Ellison" but have different ORCIDs.

**Q2.** 29 works: W01, W03, W04, W05, W06, W07, W1005, W1048, W1050, W12, W19, W206, W238, W271, W374, W449, W464, W555, W676, W763, W813, W817, W843, W869, W905, W936, W949, W972, W984. The list comes from credits for A01, and W03 is the merged W02/W03.

**Q3.** No. W37 is credited to A05 (I05) and W38 to A06 (I12), who have different ORCIDs.

**Q4.** 9 research works for A05.

**Q5.** No. W08 is by A04 and W01 by A01, who have different ORCIDs.

**Q6.** A01, with 19 research works credited to I02 and its units.

**Q7.** 19 research works for Mara Ellison (A01).

**Q8.** I01. I04 sits under I03, which sits under I02, which sits under I01.

**Q9.** 95.

**Q10.** 140.

**Q11.** 59.

**Q12.** Full counting gives 13 and fractional counting gives 11.0 for I07 in 2024.

**Q13.** Under full counting I09 has more (16 vs 13). Under fractional counting I07 has more (11.0 vs 4.0).

**Q14.** GB institutions have 46 works under full counting and 42.0 under fractional counting.

**Q15.** 25.

**Q16.** 88.

**Q17.** W04, W05, W271, W813, W936 and W949. These are editorials (EDT) and errata (ERR). W07, W374, W449 and W763 are excluded for other reasons (delisted source or retraction).

**Q18.** 15.

**Q19.** 27 distinct citing works, counting citations to both W02 and W03.

**Q20.** None. Preprints are already merged into their published versions (for example W02 into W03). The remaining PPR works have no same-title counterpart in the data.

**Q21.** 45.

**Q22.** W30 has more citations in total (16 vs 11). W31 has more within its publication year and the two years after (11 vs 2).

**Q23.** W30 has 16 citations in total and 2 within the window.

**Q24.** 12.

**Q25.** 20.

**Q26.** No. W23 is retracted and not research output.

**Q27.** I12, with 12 citing works.

**Q28.** 4.

**Q29.** Of the 27 citing works, 7 are author self-citations, 10 are I02 self-citations and 20 are country self-citations.

**Q30.** 14.

**Q31.** W1042, W337, W338, W396, W50, W51, W52, W546, W682, W801 and W895.

**Q32.** 17.

**Q33.** W20, with 13 citations.

**Q34.** 7.

**Q35.** 25.

**Q36.** 36.

**Q37.** I06. W70 is credited to I06 through author A08.

**Q38.** I04, I03, I02 and I01. W71 is credited to I04, which rolls up through I03 and I02 to I01.

**Q39.** 19 works, all in S01.

**Q40.** S01.

**Q41.** No. S03 has tier_rank 3 and S02 has tier_rank 2, and a lower rank number means a higher tier.

**Q42.** 0. S04 is delisted, so none of its works are research output.

**Q43.** No. W07 is in the delisted source S04.

**Q44.** I02, I03, I04, I05, I19 and I20. I06 is only affiliated and does not roll up.

**Q45.** 16.

**Q46.** I01, following the parent_id chain I06 to I02 to I01. This is ambiguous, because I06 is an affiliate whose works count only towards itself.

**Q47.** I09 has more works in total (16 vs 13) but they are heavily co-authored with other institutions and countries. I07 therefore overtakes it under fractional counting (11 vs 4).

**Q48.** W70's author is credited to I06, an affiliate whose institution_units row counts only towards itself. I06's works do not roll up to I02, unlike I04 (Tessel), which does roll up.

```json
{"Q1":{"count":3},"Q2":{"works":["W01","W03","W04","W05","W06","W07","W1005","W1048","W1050","W12","W19","W206","W238","W271","W374","W449","W464","W555","W676","W763","W813","W817","W843","W869","W905","W936","W949","W972","W984"]},"Q3":{"same":"no"},"Q4":{"count":9},"Q5":{"same":"no"},"Q6":{"author":"A01"},"Q7":{"count":19},"Q8":{"parent":"I01"},"Q9":{"count":95},"Q10":{"count":140},"Q11":{"count":59},"Q12":{"full":13,"fractional":11},"Q13":{"full":"I09","fractional":"I07"},"Q14":{"full":46,"fractional":42},"Q15":{"count":25},"Q16":{"count":88},"Q17":{"works":["W04","W05","W271","W813","W936","W949"]},"Q18":{"count":15},"Q19":{"count":27},"Q20":{"works":[]},"Q21":{"count":45},"Q22":{"more_in_total":"W30","more_in_window":"W31"},"Q23":{"total":16,"in_window":2},"Q24":{"count":12},"Q25":{"count":20},"Q26":{"counts":"no"},"Q27":{"institution":"I12","citing_works":12},"Q28":{"count":4},"Q29":{"author":7,"institution":10,"country":20},"Q30":{"count":14},"Q31":{"works":["W1042","W337","W338","W396","W50","W51","W52","W546","W682","W801","W895"]},"Q32":{"count":17},"Q33":{"work":"W20"},"Q34":{"count":7},"Q35":{"count":25},"Q36":{"count":36},"Q37":{"institution":"I06"},"Q38":{"institutions":["I04","I03","I02","I01"]},"Q39":{"count":19},"Q40":{"source":"S01"},"Q41":{"higher":"no"},"Q42":{"count":0},"Q43":{"research":"no"},"Q44":{"institutions":["I02","I03","I04","I05","I19","I20"]},"Q45":{"count":16},"Q46":{"parent":"I01"}}
```