D3 sql+executed, run 4: the query session

22 queries1 failed6 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 sources order by source_id;
```
```sql
select * from institution_units order by institution_id, levels_up;
```
```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:

| 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 3:

| 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 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">
select type, research_output, retracted, count(*) from works group by all order by 1,2,3;
</invoke>

<invoke name="sql">
select w.*, string_agg(c.person_id||'@'||c.institution_id, ', ') cr from works w left join credits c using(work_id) where work_id in ('W01','W08','W37','W38','W07','W23','W30','W31','W70','W71') or work_id in (select work_id from credits where person_id in ('A01','A04','A102')) group by all order by work_id;
</invoke>

<invoke name="sql">
select * from works where title ilike '%Drift-aware%';
</invoke>
























































































































Wait, I wrote the queries incorrectly — I used a non-standard tool-call format instead of fenced sql blocks. Let me redo them properly.

```sql
select type, research_output, retracted, count(*) from works group by all order by 1,2,3;
```
```sql
select w.*, string_agg(c.person_id||'@'||c.institution_id, ', ') cr from works w left join credits c using(work_id) where work_id in ('W01','W08','W37','W38','W07','W23','W30','W31','W70','W71') or work_id in (select work_id from credits where person_id in ('A01','A04','A102')) group by all order by work_id;
```
```sql
select * from works where title ilike '%Drift-aware%';
```

Query results

Query 1:

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

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 |
| W1020 | Machine learning for vortex shedding in mixed-energy coasts | 2020 | ART | S13 | false | no | W1020 | A04@I07 |
| 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 |
| 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 |
| W206 | Observations of buoy telemetry in a semi-enclosed sea | 2020 | ART | S01 | false | yes | W206 | A123@I19, A01@I02 |
| W23 | Anomalous dissipation in estuarine plumes | 2023 | ART | S02 | true | no | W23 | A09@I07 |
| W238 | Observations of survey vessel autonomy in tidal inlets | 2023 | ART | S02 | false | yes | W238 | A32@I17, A111@I02, A01@I02 |
| W271 | Editorial: battery endurance | 2019 | EDT | S01 | false | no | W271 | A01@I02 |
| W30 | Tidal stream resource maps for the Dunmore coast | 2019 | ART | S03 | false | yes | W30 | A15@I13 |
| W31 | Machine-learned tidal forecasts | 2023 | ART | S01 | false | yes | W31 W42 | A17@I14 |
| W357 | Observations of tidal mixing | 2025 | ART | S10 | false | yes | W357 | A20@I09, A148@I09, A107@I09, A102@I09 |
| 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 |
| 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 |
| 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 |
| W487 | Remote sensing of wave runup in the open shelf | 2024 | ART | S11 | false | yes | W487 | A221@I30, A195@I31, A177@I25, A102@I09 |
| W555 | A model of underwater docking in a macrotidal bay | 2020 | ART | S09 | false | yes | W555 | A123@I19, A01@I02 |
| W603 | Remote sensing of sediment resuspension in shallow channels | 2025 | ART | S02 | false | yes | W603 | A04@I07 |
| W631 | Low-cost methods for sediment flux | 2024 | ART | S01 | false | yes | W631 | A34@I15, A213@I25, A212@I24, A102@I09 |
| W676 | Low-cost methods for turbidity sensing | 2025 | ART | S02 | false | yes | W676 | A01@I02 |
| W684 | Field measurements of dune erosion in tidal inlets | 2024 | ART | S03 | false | yes | W684 | A226@I24, A187@I18, A174@I17, A102@I09 |
| W696 | Scaling laws for tidal mixing | 2019 | ART | S06 | false | yes | W696 | A18@I09, A152@I09, A150@I09, A102@I09 |
| W70 | Optical backscatter in turbid estuaries | 2023 | ART | S02 | false | yes | W70 | A08@I06 |
| W71 | Low-power optical sensors for gliders | 2024 | ART | S01 | false | yes | W71 | A08@I04 |
| W720 | Revisiting tidal mixing | 2019 | ART | S01 | false | yes | W720 | A34@I15, A04@I07 |
| W763 | A model of glider navigation in a macrotidal bay | 2024 | ART | S13 | false | no | W763 | A110@I02, A01@I02 |
| W813 | Erratum: Remote sensing of buoy telemetry in fjords | 2025 | ERR | S02 | false | no | W813 | A198@I17, A111@I02, A01@I02 |
| W817 | Remote sensing of survey vessel autonomy in the Brevin shelf | 2023 | ART | S10 | false | yes | W817 | A32@I17, A120@I04, A01@I02 |
| W843 | A framework for ocean optics in the Albury estuary | 2023 | ART | S02 | false | yes | W843 | A164@I14, A01@I02 |
| W869 | A model of survey vessel autonomy in mixed-energy coasts | 2023 | ART | S02 | false | yes | W612 W869 | A191@I27, A125@I19, A01@I02 |
| W890 | Seasonal variability in buoy telemetry | 2023 | ART | S03 | false | yes | W890 | A04@I07 |
| W902 | Low-cost methods for estuarine salinity | 2021 | ART | S03 | false | yes | W210 W902 | A102@I09 |
| W905 | Machine learning for battery endurance in a laboratory flume | 2025 | ART | S01 | false | yes | W905 | A118@I03, A01@I02 |
| W936 | Editorial: fleet coordination | 2019 | EDT | S01 | false | no | W936 | A01@I02 |
| W948 | A framework for wave runup in fjords | 2021 | ART | S01 | false | yes | W948 | A200@I24, A19@I09, A153@I23, A149@I09, A102@I09 |
| W949 | Erratum: Observations of survey vessel autonomy in tidal inlets | 2024 | ERR | S02 | false | no | W949 | A32@I17, A111@I02, A01@I02 |
| W972 | Long-term trends in acoustic positioning | 2020 | ART | S02 | false | yes | W972 | A221@I30, A117@I03, A01@I02 |
| W984 | A model of survey vessel autonomy | 2024 | ART | S01 | false | yes | W984 | A210@I17, A01@I02 |
| W985 | Remote sensing of vortex shedding | 2023 | ART | S09 | false | yes | W985 | A215@I27, A04@I07 |
| W998 | Machine learning for wave boundary layers in harbour basins | 2022 | ART | S01 | false | yes | W998 | A137@I07, A04@I07 |

Query 3:

| work_id | title | year | type | source_id | retracted | research_output | record_ids |
|---|---|---|---|---|---|---|---|
| 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 |

Run more queries, or give the final answers.

The model

```sql
with pm as (select person_id, unnest(string_split(author_ids,' ')) aid from people),
cr as (select distinct c.work_id, pm.person_id pid, c.institution_id inst from credits c join pm on c.person_id=pm.aid),
n as (select work_id, count(distinct pid) n from cr group by 1),
x as (select distinct cr.work_id, pid, iu.counts_towards t, w.year, n.n from cr join works w using(work_id) join n using(work_id) join institution_units iu on iu.institution_id=cr.inst where w.research_output='yes')
select t, count(distinct work_id) full_all, count(distinct work_id) filter(year='2024') f24, round(sum(1.0/n) filter(year='2024'),3) frac24, count(distinct work_id) filter(year='2025') f25 from x group by t order by t;
```
```sql
with pm as (select person_id, unnest(string_split(author_ids,' ')) aid from people),
cr as (select distinct c.work_id, pm.person_id pid, c.institution_id inst from credits c join pm on c.person_id=pm.aid),
n as (select work_id, count(distinct pid) n from cr group by 1),
x as (select distinct cr.work_id, pid, i.country t, w.year, n.n from cr join works w using(work_id) join n using(work_id) join institutions i on i.institution_id=cr.inst where w.research_output='yes')
select t, count(distinct work_id) f24, round(sum(1.0/n),3) frac24 from x where year='2024' group by t order by t;
```
```sql
with pm as (select person_id, unnest(string_split(author_ids,' ')) aid from people),
cr as (select distinct c.work_id, pm.person_id pid, c.institution_id inst from credits c join pm on c.person_id=pm.aid),
a as (select pid, count(distinct work_id) filter(where w.research_output='yes') res, count(distinct work_id) all_w, count(distinct work_id) filter(where w.research_output='yes' and inst in (select institution_id from institution_units where counts_towards='I02')) res_i02 from cr join works w using(work_id) group by 1)
select * from a where pid in ('A01','A04','A102','A05','A06','A105','A07','A108','A09') or res_i02>=(select max(res_i02) from a)-1 order by res_i02 desc;
```
```sql
select count(*) bad_cred from credits where person_id not in (select unnest(string_split(author_ids,' ')) from people);
select count(*) bad_cited from citations where cited_work_id not in (select work_id from works);
select count(*) bad_citing from citations where citing_work_id not in (select work_id from works);
select count(*) dup from (select citing_work_id, cited_work_id from citations group by all having count(*)>1);
```
```sql
select 'W01 cited by' k, string_agg(citing_work_id, ',') v from citations where cited_work_id='W01'
union all select 'W03 cited by', string_agg(citing_work_id, ',') from citations where cited_work_id in ('W03','W02')
union all select 'W30 cited by', string_agg(citing_work_id||':'||w.year, ',') from citations c join works w on w.work_id=c.citing_work_id where cited_work_id='W30'
union all select 'W31 cited by', string_agg(citing_work_id||':'||w.year, ',') from citations c join works w on w.work_id=c.citing_work_id where cited_work_id in ('W31','W42')
union all select 'W30/31 pub', string_agg(work_id||':'||year, ',') from works where work_id in ('W30','W31')
union all select 'preprints', string_agg(work_id||':'||record_ids||':'||type||':'||source_id, ' | ') from works where type='PPR' or source_id in ('S05','S14');
```

Query results

Query 1:

| t | full_all | f24 | frac24 | f25 |
|---|---|---|---|---|
| I01 | 140 | 34 | 29.333 | 16 |
| I02 | 95 | 25 | 21.333 | 12 |
| I03 | 58 | 11 | 8.5 | 6 |
| I04 | 23 | 5 | 3.167 | 1 |
| I05 | 46 | 10 | 8.0 | 4 |
| I06 | 16 | 2 | 2.0 | 3 |
| I07 | 59 | 13 | 11.0 | 7 |
| I08 | 25 | 6 | 4.25 | 3 |
| I09 | 48 | 16 | 4.0 | 4 |
| I10 | 12 | 4 | 2.083 | 0 |
| I11 | 38 | 9 | 6.25 | 12 |
| I12 | 64 | 14 | 11.167 | 14 |
| I13 | 39 | 11 | 9.0 | 10 |
| I14 | 45 | 16 | 10.75 | 6 |
| I15 | 59 | 7 | 3.083 | 8 |
| I16 | 81 | 25 | 12.667 | 12 |
| I17 | 85 | 18 | 8.917 | 7 |
| I18 | 63 | 12 | 4.833 | 7 |
| I19 | 26 | 7 | 5.167 | 4 |
| I20 | 15 | 1 | 1.0 | 2 |
| I21 | 8 | 2 | 1.667 | 0 |
| I22 | 9 | 1 | 1.0 | 0 |
| I23 | 9 | 1 | 0.25 | 1 |
| I24 | 56 | 10 | 4.667 | 7 |
| I25 | 30 | 8 | 3.917 | 5 |
| I26 | 40 | 11 | 5.417 | 4 |
| I27 | 23 | 7 | 4.0 | 2 |
| I28 | 27 | 5 | 2.5 | 3 |
| I29 | 21 | 2 | 0.583 | 2 |
| I30 | 52 | 8 | 4.25 | 11 |
| I31 | 38 | 9 | 3.917 | 5 |

Query 2:

| t | f24 | frac24 |
|---|---|---|
| AU | 7 | 4.0 |
| CA | 18 | 8.917 |
| DE | 14 | 12.0 |
| ES | 12 | 4.833 |
| FR | 22 | 17.417 |
| GB | 46 | 42.0 |
| IT | 8 | 3.917 |
| JP | 25 | 12.667 |
| KR | 2 | 0.583 |
| NL | 30 | 16.833 |
| NO | 5 | 2.5 |
| PL | 11 | 5.417 |
| PT | 8 | 4.25 |
| US | 24 | 11.667 |

Query 3:

| pid | res | all_w | res_i02 |
|---|---|---|---|
| A01 | 19 | 29 | 19 |
| A07 | 15 | 16 | 15 |
| A05 | 9 | 9 | 0 |
| A06 | 7 | 7 | 0 |
| A108 | 6 | 7 | 0 |
| A04 | 8 | 9 | 0 |
| A09 | 12 | 15 | 0 |
| A102 | 9 | 9 | 0 |
| A105 | 3 | 3 | 0 |

Query 4:

| dup |
|---|
| 0 |

Query 5:

| k | v |
|---|---|
| W01 cited by | W40,W41,W229,W602,W657,W660,W895 |
| W03 cited by | W12,W13,W14,W15,W16,W17,W18,W214,W273,W315,W349,W535,W544,W558,W598,W676,W711,W748,W761,W812,W833,W843,W889,W890,W931,W1048,W1056 |
| W30 cited by | W60:2020,W61:2022,W62:2023,W63:2024,W64:2025,W246:2023,W274:2024,W276:2022,W289:2022,W334:2023,W439:2022,W856:2024,W894:2024,W988:2021,W992:2025,W1033:2025 |
| W31 cited by | W62:2023,W65:2024,W66:2025,W67:2023,W249:2025,W318:2025,W650:2024,W757:2023,W794:2025,W851:2023,W1039:2023 |
| W30/31 pub | W30:2019,W31:2023 |
| preprints | W18:W18:PPR:S05 | W221:W221:PPR:S14 | W306:W306:PPR:S14 | W336:W336:PPR:S14 | W566:W566:PPR:S05 | W745:W745:PPR:S05 | W747:W747:PPR:S14 | W780:W780:PPR:S05 | W781:W781:PPR:S05 | W824:W824:PPR:S14 | W1054:W1054:PPR:S05 |

Run more queries, or give the final answers.

The model

```sql
with cr as (select distinct c.work_id, p.person_id pid, c.institution_id inst from credits c join people p on list_contains(string_split(p.author_ids,' '), c.person_id))
select string_agg(w.work_id||':'||w.type||':'||w.research_output||':'||w.retracted||':'||w.source_id, ' ' order by w.work_id) from works w where work_id in (select work_id from cr where pid='A01');
```
```sql
select source_id, count(*) filter(where research_output='yes') res, count(*) filter(where research_output='yes' and year='2024') res24 from works where source_id in ('S01','S02','S04','S06') group by 1;
```
```sql
with cr as (select distinct c.work_id, p.person_id pid, c.institution_id inst from credits c join people p on list_contains(string_split(p.author_ids,' '), c.person_id)),
a09 as (select distinct work_id from cr join works using(work_id) where pid='A09' and research_output='yes')
select count(*) cites, count(distinct cited_work_id) from citations where cited_work_id in (select work_id from a09);
```
```sql
with cr as (select distinct c.work_id, p.person_id pid, c.institution_id inst from credits c join people p on list_contains(string_split(p.author_ids,' '), c.person_id)),
kw as (select distinct work_id from cr join works using(work_id) join institution_units iu on iu.institution_id=cr.inst where iu.counts_towards='I07' and research_output='yes'),
ci as (select distinct c.citing_work_id, c.cited_work_id from citations c where cited_work_id in (select work_id from kw)),
ti as (select distinct citing_work_id w, iu.counts_towards t from ci join cr on cr.work_id=ci.citing_work_id join institution_units iu on iu.institution_id=cr.inst)
select 'inst' k, t, count(distinct w) n from ti where t<>'I07' group by t order by n desc limit 5;
```
```sql
with cr as (select distinct c.work_id, p.person_id pid, c.institution_id inst from credits c join people p on list_contains(string_split(p.author_ids,' '), c.person_id)),
kw as (select distinct work_id from cr join works using(work_id) join institution_units iu on iu.institution_id=cr.inst where iu.counts_towards='I07' and research_output='yes')
select 'Q28' k, count(distinct c.citing_work_id) from citations c join cr on cr.work_id=c.citing_work_id join institution_units iu on iu.institution_id=cr.inst where iu.counts_towards='I09' and c.cited_work_id in (select work_id from kw)
union all
select 'Q33 '||cited_work_id, count(*) from citations where cited_work_id in (select work_id from kw) group by cited_work_id
order by 2 desc limit 6;
```
```sql
with cr as (select distinct c.work_id, p.person_id pid, c.institution_id inst, i.country from credits c join people p on list_contains(string_split(p.author_ids,' '), c.person_id) join institutions i on i.institution_id=c.institution_id),
ci as (select distinct citing_work_id w from citations where cited_work_id in ('W03','W02'))
select count(*) filter(where exists(select 1 from cr a join cr b on a.pid=b.pid where a.work_id=ci.w and b.work_id='W03')) author_self,
count(*) filter(where exists(select 1 from cr a join institution_units u1 on u1.institution_id=a.inst join cr b on 1=1 join institution_units u2 on u2.institution_id=b.inst and u2.counts_towards=u1.counts_towards where a.work_id=ci.w and b.work_id='W03' and u1.counts_towards='I02')) inst_i02,
count(*) filter(where exists(select 1 from cr a join institution_units u1 on u1.institution_id=a.inst join cr b on 1=1 join institution_units u2 on u2.institution_id=b.inst and u2.counts_towards=u1.counts_towards where a.work_id=ci.w and b.work_id='W03' and u1.counts_towards='I01')) inst_i01,
count(*) filter(where exists(select 1 from cr a join cr b on a.country=b.country where a.work_id=ci.w and b.work_id='W03')) country_self, count(*) n from ci;
```
```sql
with h1 as (select citing_work_id w from citations where cited_work_id='W01'),
h2 as (select distinct citing_work_id w from citations where cited_work_id in (select w from h1))
select (select string_agg(w, ',' order by w) from h2) q31, (select count(*) from (select w from h1 union select w from h2)) q32;
```
```sql
select string_agg(distinct record_ids, ' | ') from works where record_ids like '% %';
```

Query results

Query 1:

| string_agg(((((((((w.work_id || ':') || w."type") || ':') || w.research_output) || ':') || w.retracted) || ':') || w.source_id), ' ' ORDER BY w.work_id) |
|---|
| W01:ART:yes:false:S01 W03:ART:yes:false:S02 W04:EDT:no:false:S01 W05:ERR:no:false:S02 W06:ART:yes:false:S03 W07:ART:no:false:S04 W1005:ART:yes:false:S02 W1048:ART:yes:false:S10 W1050:ART:yes:false:S09 W12:ART:yes:false:S02 W19:ART:yes:false:S02 W206:ART:yes:false:S01 W238:ART:yes:false:S02 W271:EDT:no:false:S01 W374:ART:no:true:S02 W449:ART:no:false:S04 W464:ART:yes:false:S09 W555:ART:yes:false:S09 W676:ART:yes:false:S02 W763:ART:no:false:S13 W813:ERR:no:false:S02 W817:ART:yes:false:S10 W843:ART:yes:false:S02 W869:ART:yes:false:S02 W905:ART:yes:false:S01 W936:EDT:no:false:S01 W949:ERR:no:false:S02 W972:ART:yes:false:S02 W984:ART:yes:false:S01 |

Query 2:

| source_id | res | res24 |
|---|---|---|
| S01 | 88 | 24 |
| S06 | 36 | 9 |
| S02 | 110 | 25 |
| S04 | 0 | 0 |

Query 3:

| cites | count(DISTINCT cited_work_id) |
|---|---|
| 20 | 6 |

Query 4:

| k | t | n |
|---|---|---|
| inst | I12 | 12 |
| inst | I01 | 5 |
| inst | I11 | 4 |
| inst | I09 | 4 |
| inst | I02 | 3 |

Query 5:

| k | count(DISTINCT c.citing_work_id) |
|---|---|
| Q33 W20 | 13 |
| Q28 | 4 |
| Q33 W417 | 2 |
| Q33 W981 | 2 |
| Q33 W707 | 2 |
| Q33 W855 | 2 |

Query 6:

| author_self | inst_i02 | inst_i01 | country_self | n |
|---|---|---|---|---|
| 7 | 10 | 14 | 20 | 27 |

Query 7:

| q31 | q32 |
|---|---|
| W1042,W337,W338,W396,W50,W51,W52,W546,W682,W801,W895 | 17 |

Query 8:

| string_agg(DISTINCT record_ids, ' | ') |
|---|
| W303 W730 | W612 W869 | W432 W879 | W665 W728 | W219 W251 | W263 W279 | W464 W663 | W210 W902 | W322 W453 | W416 W640 | W1038 W556 | W524 W807 | W877 W965 | W301 W611 | W312 W522 | W422 W506 | W545 W742 | W31 W42 | W226 W710 | W02 W03 | W460 W496 | W1049 W789 | W1027 W407 |

Run more queries, or give the final answers.

The model

```sql
with cr as (select distinct c.work_id, p.person_id pid, c.institution_id inst from credits c join people p on list_contains(string_split(p.author_ids,' '), c.person_id))
select (select count(distinct work_id) from cr join works using(work_id) join institution_units iu on iu.institution_id=cr.inst where iu.counts_towards='I02' and research_output='yes' and source_id='S01') q39,
(select string_agg(person_id||':'||name,' | ') from people where name ilike '%ellison%') names;
```

Query results

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

Run more queries, or give the final answers.

The model

```sql
with cr as (select distinct c.work_id, c.institution_id inst from credits c)
select (select count(distinct work_id) from cr join works using(work_id) join institution_units iu on iu.institution_id=cr.inst where iu.counts_towards='I02' and research_output='yes' and source_id='S01') q39,
(select string_agg(person_id||':'||name||':'||author_ids,' | ') from people where name ilike '%ellison%') ell,
(select string_agg(p.work_id||'~'||w.work_id,' | ') from works p join works w on lower(trim(p.title))=lower(trim(w.title)) and p.work_id<>w.work_id where p.type='PPR') ppr;
```

Query results

Query 1:

| q39 | ell | ppr |
|---|---|---|
| 19 | A01:Mara Ellison:A01 A02 A03 A101 | A04:M. Ellison:A04 | A102:M. Ellison:A102 |  |

Run more queries, or give the final answers.

The model

**Q1.** 3. "Mara Ellison" and "Ellison, Mara J." both fall under A01, whose merged author records are A01, A02, A03 and A101. "M. Ellison" is two different people, A04 and A102, who 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. These are all works credited to A01, any type.
**Q3.** No. W37 is by A05 and W38 by A06, who have different ORCIDs and different institutions.
**Q4.** 9. These are A05's research works.
**Q5.** No. The M. Ellison on W08 is A04, a separate person from A01 with a different ORCID.
**Q6.** A01, with 19 research works credited to I02 and its units (A07 has 15).
**Q7.** 19.
**Q8.** I01. The chain is I04 → I03 → I02 → I01.
**Q9.** 95.
**Q10.** 140.
**Q11.** 59.
**Q12.** Full counting gives 13 and fractional counting gives 11.0 for I07 in 2024.
**Q13.** Full counting: I09 (16 vs 13). Fractional counting: I07 (11.0 vs 4.0).
**Q14.** GB institutions had 46 works under full counting and 42.0 under fractional counting in 2024.
**Q15.** 25.
**Q16.** 88.
**Q17.** W04, W05, W271, W813, W936 and W949. These are editorials and errata. Her other non-research works are excluded for other reasons: W07, W449 and W763 are in delisted sources, and W374 is retracted.
**Q18.** 15.
**Q19.** 27, counting citations of both W02 and W03 (merged as W03).
**Q20.** None. Preprints are already merged into their published versions, and no preprint record matches another work's title.
**Q21.** 45.
**Q22.** More in total: W30 (16 vs 11). More in the window of its publication year plus two years: W31 (11 vs 1).
**Q23.** W30 has 16 citations in total and 1 within 2019–2021 (W988).
**Q24.** 12.
**Q25.** 20, across 6 of her cited research works.
**Q26.** No. W23 is retracted and not research output.
**Q27.** I12, with 12 citing works.
**Q28.** 4.
**Q29.** 7 are author self-citations, 10 are University of Albury self-citations, and 20 are country self-citations.
**Q30.** 14.
**Q31.** W1042, W337, W338, W396, W50, W51, W52, W546, W682, W801, W895.
**Q32.** 17.
**Q33.** W20, with 13 citations.
**Q34.** 7.
**Q35.** 25.
**Q36.** 36.
**Q37.** I06, the Halloran Institute for Coastal Studies.
**Q38.** I04, I03, I02 and I01.
**Q39.** 19. This counts the University of Albury's research works in S01, the only highest-tier source (tier_rank 1).
**Q40.** S01.
**Q41.** No. S03 is tier K1 (rank 3) and S02 is K2 (rank 2), so S02 is higher.
**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, I19, I05 and I20. I06 is only an affiliate: institution_units has it counting towards itself alone.
**Q45.** 16.
**Q46.** I06. It is linked to I02 only as an affiliate, and its works count towards no one else.
**Q47.** I09's 16 works in 2024 mostly have many co-authors, so its fractional total is only 4.0. I07's 13 works have fewer authors each, giving 11.0.
**Q48.** I06 is an affiliate, so it does not count towards I02. W70 is credited to I06, not to I04, which would roll up to I02.

```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.0},"Q13":{"full":"I09","fractional":"I07"},"Q14":{"full":46,"fractional":42.0},"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":1},"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","I19","I05","I20"]},"Q45":{"count":16},"Q46":{"parent":"I06"}}
```