D1 sql, run 5: the query session

## 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?

I'll start by exploring the schema and the value distributions.

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

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:

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

Query 4:

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

Run more queries, or give the final answers.

```sql
SELECT a.author_id, a.name, a.orcid, s.work_id, w.type, w.year, w.is_retracted, w.record_source, w.version_of, w.source_id, s.author_position, s.institution_id, s.affil_seq FROM authors a JOIN authorships s USING(author_id) JOIN works w USING(work_id) WHERE a.author_id IN ('A01','A02','A03','A04','A101','A102','A05','A06','A104','A105') ORDER BY a.author_id, s.work_id;
```
```sql
SELECT * FROM works WHERE work_id IN ('W01','W07','W23','W30','W31','W37','W38','W70','W71','W08') OR title ILIKE '%Drift-aware%' ORDER BY work_id;
```
```sql
SELECT * FROM works WHERE version_of IS NOT NULL ORDER BY work_id;
```
```sql
SELECT * FROM authorships WHERE work_id IN ('W70','W71','W23','W07') ORDER BY work_id, author_position, affil_seq;
```

Query 1:

| author_id | name | orcid | work_id | type | year | is_retracted | record_source | version_of | source_id | author_position | institution_id | affil_seq |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A01 | Mara Ellison | 0000-0002-9841-6229 | W01 | ART | 2021 | false | FEED |  | S01 | 1 | I02 | 1 |
| A01 | Mara Ellison | 0000-0002-9841-6229 | W04 | EDT | 2023 | false | FEED |  | S01 | 1 | I02 | 1 |
| A01 | Mara Ellison | 0000-0002-9841-6229 | W05 | ERR | 2024 | false | FEED |  | S02 | 1 | I02 | 1 |
| A01 | Mara Ellison | 0000-0002-9841-6229 | W06 | ART | 2024 | false | FEED |  | S03 | 1 | I02 | 1 |
| A01 | Mara Ellison | 0000-0002-9841-6229 | W1005 | ART | 2025 | false | FEED |  | S02 | 1 | I02 | 1 |
| A01 | Mara Ellison | 0000-0002-9841-6229 | W12 | ART | 2024 | false | CRIS |  | S02 | 1 | I02 | 1 |
| A01 | Mara Ellison | 0000-0002-9841-6229 | W238 | ART | 2023 | false | FEED |  | S02 | 1 | I02 | 1 |
| A01 | Mara Ellison | 0000-0002-9841-6229 | W271 | EDT | 2019 | false | FEED |  | S01 | 1 | I02 | 1 |
| A01 | Mara Ellison | 0000-0002-9841-6229 | W374 | ART | 2023 | true | FEED |  | S02 | 1 | I02 | 1 |
| A01 | Mara Ellison | 0000-0002-9841-6229 | W464 | ART | 2021 | false | FEED |  | S09 | 1 | I02 | 1 |
| A01 | Mara Ellison | 0000-0002-9841-6229 | W676 | ART | 2025 | false | FEED |  | S02 | 1 | I02 | 1 |
| A01 | Mara Ellison | 0000-0002-9841-6229 | W813 | ERR | 2025 | false | FEED |  | S02 | 1 | I02 | 1 |
| A01 | Mara Ellison | 0000-0002-9841-6229 | W949 | ERR | 2024 | false | FEED |  | S02 | 1 | I02 | 1 |
| A02 | M. Ellison | 0000-0002-9841-6229 | W02 | PPR | 2022 | false | FEED | W03 | S05 | 1 | I02 | 1 |
| A02 | M. Ellison | 0000-0002-9841-6229 | W07 | ART | 2020 | false | FEED |  | S04 | 1 | I02 | 1 |
| A02 | M. Ellison | 0000-0002-9841-6229 | W1048 | ART | 2023 | false | FEED |  | S10 | 1 | I02 | 1 |
| A02 | M. Ellison | 0000-0002-9841-6229 | W1050 | ART | 2024 | false | FEED |  | S09 | 1 | I02 | 1 |
| A02 | M. Ellison | 0000-0002-9841-6229 | W19 | ART | 2025 | false | CRIS |  | S02 | 1 | I02 | 1 |
| A02 | M. Ellison | 0000-0002-9841-6229 | W449 | ART | 2022 | false | FEED |  | S04 | 1 | I02 | 1 |
| A02 | M. Ellison | 0000-0002-9841-6229 | W663 | PPR | 2021 | false | FEED | W464 | S14 | 1 | I02 | 1 |
| A02 | M. Ellison | 0000-0002-9841-6229 | W869 | ART | 2023 | false | FEED |  | S02 | 1 | I02 | 1 |
| A02 | M. Ellison | 0000-0002-9841-6229 | W972 | ART | 2020 | false | FEED |  | S02 | 1 | I02 | 1 |
| A03 | Ellison, Mara J. | 0000-0002-9841-6229 | W03 | ART | 2023 | false | FEED |  | S02 | 1 | I02 | 1 |
| A03 | Ellison, Mara J. | 0000-0002-9841-6229 | W206 | ART | 2020 | false | FEED |  | S01 | 1 | I02 | 1 |
| A03 | Ellison, Mara J. | 0000-0002-9841-6229 | W763 | ART | 2024 | false | FEED |  | S13 | 1 | I02 | 1 |
| A03 | Ellison, Mara J. | 0000-0002-9841-6229 | W817 | ART | 2023 | false | FEED |  | S10 | 1 | I02 | 1 |
| A04 | M. Ellison | 0000-0004-8662-8360 | W08 | ART | 2025 | false | FEED |  | S01 | 1 | I07 | 1 |
| A04 | M. Ellison | 0000-0004-8662-8360 | W1020 | ART | 2020 | false | FEED |  | S13 | 1 | I07 | 1 |
| A04 | M. Ellison | 0000-0004-8662-8360 | W16 | ART | 2025 | false | FEED |  | S02 | 1 | I07 | 1 |
| A04 | M. Ellison | 0000-0004-8662-8360 | W428 | ART | 2022 | false | FEED |  | S03 | 1 | I07 | 1 |
| A04 | M. Ellison | 0000-0004-8662-8360 | W603 | ART | 2025 | false | FEED |  | S02 | 1 | I07 | 1 |
| A04 | M. Ellison | 0000-0004-8662-8360 | W720 | ART | 2019 | false | FEED |  | S01 | 1 | I07 | 1 |
| A04 | M. Ellison | 0000-0004-8662-8360 | W890 | ART | 2023 | false | FEED |  | S03 | 1 | I07 | 1 |
| A04 | M. Ellison | 0000-0004-8662-8360 | W985 | ART | 2023 | false | FEED |  | S09 | 1 | I07 | 1 |
| A04 | M. Ellison | 0000-0004-8662-8360 | W998 | ART | 2022 | false | FEED |  | S01 | 1 | I07 | 1 |
| A05 | Jonas Brandt | 0000-0001-6105-5008 | W1038 | ART | 2020 | false | FEED |  | S02 | 1 | I05 | 1 |
| A05 | Jonas Brandt | 0000-0001-6105-5008 | W14 | ART | 2025 | false | FEED |  | S03 | 1 | I05 | 1 |
| A05 | Jonas Brandt | 0000-0001-6105-5008 | W37 | ART | 2022 | false | FEED |  | S03 | 1 | I05 | 1 |
| A05 | Jonas Brandt | 0000-0001-6105-5008 | W554 | ART | 2024 | false | FEED |  | S01 | 1 | I05 | 1 |
| A05 | Jonas Brandt | 0000-0001-6105-5008 | W565 | ART | 2019 | false | FEED |  | S03 | 1 | I05 | 1 |
| A05 | Jonas Brandt | 0000-0001-6105-5008 | W785 | REV | 2020 | false | FEED |  | S09 | 1 | I05 | 1 |
| A05 | Jonas Brandt | 0000-0001-6105-5008 | W792 | ART | 2023 | false | FEED |  | S06 | 1 | I05 | 1 |
| A06 | Jonas Brandt | 0000-0007-5475-1779 | W1031 | ART | 2025 | false | FEED |  | S01 | 1 | I12 | 1 |
| A06 | Jonas Brandt | 0000-0007-5475-1779 | W28 | ART | 2024 | false | FEED |  | S02 | 4 | I12 | 1 |
| A06 | Jonas Brandt | 0000-0007-5475-1779 | W34 | ART | 2025 | false | FEED |  | S03 | 1 | I12 | 1 |
| A06 | Jonas Brandt | 0000-0007-5475-1779 | W38 | ART | 2023 | false | FEED |  | S02 | 1 | I12 | 1 |
| A06 | Jonas Brandt | 0000-0007-5475-1779 | W384 | ART | 2025 | false | FEED |  | S10 | 1 | I12 | 1 |
| A06 | Jonas Brandt | 0000-0007-5475-1779 | W525 | ART | 2024 | false | FEED |  | S02 | 1 | I12 | 1 |
| A06 | Jonas Brandt | 0000-0007-5475-1779 | W835 | ART | 2020 | false | FEED |  | S09 | 1 | I12 | 1 |
| A101 | M. Ellison | 0000-0002-9841-6229 | W555 | ART | 2020 | false | FEED |  | S09 | 1 | I02 | 1 |
| A101 | M. Ellison | 0000-0002-9841-6229 | W612 | PPR | 2023 | false | FEED | W869 | S05 | 1 | I02 | 1 |
| A101 | M. Ellison | 0000-0002-9841-6229 | W843 | ART | 2023 | false | FEED |  | S02 | 1 | I02 | 1 |
| A101 | M. Ellison | 0000-0002-9841-6229 | W905 | ART | 2025 | false | FEED |  | S01 | 1 | I02 | 1 |
| A101 | M. Ellison | 0000-0002-9841-6229 | W936 | EDT | 2019 | false | FEED |  | S01 | 1 | I02 | 1 |
| A101 | M. Ellison | 0000-0002-9841-6229 | W984 | ART | 2024 | false | FEED |  | S01 | 1 | I02 | 1 |
| A102 | M. Ellison | 0000-0008-4738-8328 | W1056 | ART | 2023 | false | FEED |  | S02 | 1 | I09 | 1 |
| A102 | M. Ellison | 0000-0008-4738-8328 | W210 | PPR | 2021 | false | FEED | W902 | S14 | 1 | I09 | 1 |
| A102 | M. Ellison | 0000-0008-4738-8328 | W357 | ART | 2025 | false | FEED |  | S10 | 3 | I09 | 1 |
| A102 | M. Ellison | 0000-0008-4738-8328 | W422 | ART | 2023 | false | FEED |  | S01 | 2 | I09 | 1 |
| A102 | M. Ellison | 0000-0008-4738-8328 | W487 | ART | 2024 | false | FEED |  | S11 | 1 | I09 | 1 |
(12 more rows not shown)

Query 2:

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

| 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 |
| W1049 | Field measurements of tidal mixing | 2021 | PPR | S14 | 10.55555/s14.2021.1460 | W789 | false | FEED |
| W210 | Low-cost methods for estuarine salinity | 2021 | PPR | S14 | 10.55555/s14.2021.5188 | W902 | false | FEED |
| W219 | Remote sensing of sensor calibration | 2022 | PPR | S05 | 10.55555/s05.2022.9710 | W251 | false | FEED |
| W226 | Uncertainty in estuarine plumes estimates | 2023 | PPR | S05 | 10.55555/s05.2023.4761 | W710 | false | FEED |
| W263 | Low-cost methods for storm surge in shallow channels | 2019 | PPR | S05 | 10.55555/s05.2019.4392 | W279 | false | FEED |
| W322 | A framework for fleet coordination | 2019 | PPR | S14 | 10.55555/s14.2019.9512 | W453 | false | FEED |
| W407 | Low-cost methods for bed shear stress in fjords: a review | 2023 | PPR | S14 | 10.55555/s14.2023.2990 | W1027 | false | FEED |
| W416 | Uncertainty in turbidity sensing estimates in a semi-enclosed sea | 2020 | PPR | S14 | 10.55555/s14.2020.9254 | W640 | false | FEED |
| W42 | Machine-learned tidal forecasts | 2023 | PPR | S05 | 10.55555/s05.2023.6876 | W31 | false | FEED |
| W432 | Revisiting tidal asymmetry in tidal inlets | 2023 | PPR | S05 | 10.55555/s05.2023.4384 | W879 | false | FEED |
| W460 | Low-cost methods for sensor calibration | 2022 | PPR | S14 | 10.55555/s14.2022.8065 | W496 | false | FEED |
| W506 | Remote sensing of coastal upwelling | 2022 | PPR | S05 | 10.55555/s05.2022.7734 | W422 | false | FEED |
| W522 | Observations of estuarine plumes | 2020 | PPR | S14 | 10.55555/s14.2020.4281 | W312 | false | FEED |
| W524 | Field measurements of harbour seiches in the North Sea | 2020 | PPR | S05 | 10.55555/s05.2020.8964 | W807 | false | FEED |
| W545 | Field measurements of beach profiles | 2019 | PPR | S14 | 10.55555/s14.2019.4307 | W742 | false | FEED |
| W556 | Low-cost methods for marsh accretion | 2019 | PPR | S05 | 10.55555/s05.2019.3044 | W1038 | false | FEED |
| W611 | A framework for wave boundary layers in shallow channels | 2019 | PPR | S05 | 10.55555/s05.2019.5980 | W301 | false | FEED |
| W612 | A model of survey vessel autonomy in mixed-energy coasts | 2023 | PPR | S05 | 10.55555/s05.2023.6455 | W869 | false | FEED |
| W663 | A framework for turbidity sensing | 2021 | PPR | S14 | 10.55555/s14.2021.4360 | W464 | false | FEED |
| W728 | Uncertainty in wave boundary layers estimates | 2019 | PPR | S05 | 10.55555/s05.2019.7711 | W665 | false | FEED |
| W730 | A framework for tidal asymmetry in the Albury estuary | 2020 | PPR | S05 | 10.55555/s05.2020.4764 | W303 | false | FEED |
| W877 | A model of breaking waves in the North Sea | 2023 | PPR | S05 | 10.55555/s05.2023.7929 | W965 | false | FEED |

Query 4:

| work_id | author_id | author_position | institution_id | affil_seq |
|---|---|---|---|---|
| W07 | A02 | 1 | I02 | 1 |
| W23 | A09 | 1 | I07 | 1 |
| W70 | A08 | 1 | I04 | 1 |
| W70 | A08 | 1 | I06 | 2 |
| W71 | A08 | 1 | I06 | 1 |
| W71 | A08 | 1 | I04 | 2 |

Run more queries, or give the final answers.

The last results look inconsistent with the earlier ones. The DOIs for W03 differ between runs, and queries 4 and 5 returned the wrong content. I'll re-run those parts before relying on them.

```sql
SELECT work_id, title, year, type, source_id, doi, version_of, is_retracted, record_source FROM works WHERE record_source<>'FEED' ORDER BY work_id;
```
```sql
SELECT c.citing_work_id, c.cited_work_id, w.year, w.type, w.is_retracted, w.record_source FROM citations c JOIN works w ON w.work_id=c.citing_work_id WHERE c.cited_work_id IN ('W01','W02','W03','W30','W31','W42') ORDER BY 2,1;
```
```sql
SELECT a.author_id, a.name, s.work_id, w.type, w.year, w.is_retracted, w.version_of, s.institution_id FROM authors a JOIN authorships s USING(author_id) JOIN works w USING(work_id) WHERE a.author_id IN ('A07','A108','A09','A103') ORDER BY 1,3;
```

Query 1:

| 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 |
| W372 | A framework for channel hydraulics in fjords | 2025 | SUP | S02 | 10.55555/s02.2025.3633 |  | 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 |
| W573 | Long-term trends in estuarine plumes in mixed-energy coasts | 2025 | REV | S01 | 10.55555/s01.2025.6255 |  | 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 |
| W680 | Seasonal variability in breaking waves | 2025 | REV | S02 | 10.55555/s02.2025.4880 |  | 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 |
| W734 | Uncertainty in turbulent wakes estimates in sandy coasts | 2025 | SUP | S03 | 10.55555/s03.2025.2334 |  | false | STG |
| W802 | Observations of estuarine plumes in a laboratory flume | 2025 | ART | S02 | 10.55555/s02.2025.8136 |  | false | STG |
| W819 | Machine learning for wave boundary layers in the open shelf | 2025 | SUP | S02 | 10.55555/s02.2025.9700 |  | false | STG |
| W844 | A framework for seabed classification in fjords | 2025 | ART | S01 | 10.55555/s01.2025.4503 |  | false | STG |
| W866 | Uncertainty in sediment resuspension estimates in shallow channels | 2025 | SUP | S03 | 10.55555/s03.2025.9325 |  | 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 |
| W92 | Coastal turbulence: a review | 2025 | REV | S03 | 10.55555/s03.2025.6710 |  | false | STG |
| W971 | Long-term trends in sediment resuspension in the North Sea | 2025 | SUP | S01 | 10.55555/s01.2025.4830 |  | false | STG |
| W987 | Machine learning for stratified flows | 2025 | ART | S02 | 10.55555/s02.2025.6401 |  | false | STG |
| W997 | Machine learning for breaking waves in harbour basins | 2025 | SUP | S01 | 10.55555/s01.2025.2096 |  | false | STG |

Query 2:

| citing_work_id | cited_work_id | year | type | is_retracted | record_source |
|---|---|---|---|---|---|
| W229 | W01 | 2023 | ART | false | FEED |
| W40 | W01 | 2022 | ART | false | FEED |
| W41 | W01 | 2023 | ART | false | FEED |
| W602 | W01 | 2023 | ART | false | FEED |
| W657 | W01 | 2024 | ART | false | FEED |
| W660 | W01 | 2022 | ART | false | FEED |
| W844 | W01 | 2025 | ART | false | STG |
| W895 | W01 | 2023 | ART | false | FEED |
| W1048 | W02 | 2023 | ART | false | FEED |
| W15 | W02 | 2025 | REV | false | FEED |
| W18 | W02 | 2022 | PPR | false | FEED |
| W315 | W02 | 2024 | ART | false | FEED |
| W397 | W02 | 2025 | ART | false | STG |
| W535 | W02 | 2025 | ART | false | FEED |
| W544 | W02 | 2025 | ART | false | FEED |
| W558 | W02 | 2025 | ART | false | FEED |
| W843 | W02 | 2023 | ART | false | FEED |
| W1056 | W03 | 2023 | ART | false | FEED |
| W12 | W03 | 2024 | ART | false | CRIS |
| W13 | W03 | 2024 | ART | false | FEED |
| W14 | W03 | 2025 | ART | false | FEED |
| W15 | W03 | 2025 | REV | false | FEED |
| W16 | W03 | 2025 | ART | false | FEED |
| W17 | W03 | 2024 | SUP | false | FEED |
| W214 | W03 | 2023 | ART | false | FEED |
| W273 | W03 | 2024 | ART | false | FEED |
| W315 | W03 | 2024 | ART | false | FEED |
| W349 | W03 | 2024 | ART | false | FEED |
| W397 | W03 | 2025 | ART | false | STG |
| W535 | W03 | 2025 | ART | false | FEED |
| W569 | W03 | 2025 | ART | false | STG |
| W598 | W03 | 2024 | ART | false | FEED |
| W676 | W03 | 2025 | ART | false | FEED |
| W711 | W03 | 2025 | ART | false | FEED |
| W748 | W03 | 2024 | ART | false | FEED |
| W761 | W03 | 2024 | ART | false | FEED |
| W812 | W03 | 2024 | ART | false | FEED |
| W833 | W03 | 2024 | ART | false | FEED |
| W843 | W03 | 2023 | ART | false | FEED |
| W889 | W03 | 2024 | ART | false | FEED |
| W890 | W03 | 2023 | ART | false | FEED |
| W931 | W03 | 2023 | ART | false | FEED |
| W973 | W03 | 2024 | ART | true | FEED |
| W1033 | W30 | 2025 | ART | false | FEED |
| W246 | W30 | 2023 | ART | false | FEED |
| W274 | W30 | 2024 | ART | false | FEED |
| W276 | W30 | 2022 | ART | false | FEED |
| W289 | W30 | 2022 | ART | false | FEED |
| W320 | W30 | 2024 | ART | true | FEED |
| W334 | W30 | 2023 | ART | false | FEED |
| W439 | W30 | 2022 | ART | false | FEED |
| W60 | W30 | 2020 | ART | false | FEED |
| W61 | W30 | 2022 | ART | false | FEED |
| W62 | W30 | 2023 | ART | false | FEED |
| W63 | W30 | 2024 | ART | false | FEED |
| W64 | W30 | 2025 | ART | false | FEED |
| W856 | W30 | 2024 | ART | false | FEED |
| W878 | W30 | 2025 | ART | false | STG |
| W894 | W30 | 2024 | ART | false | FEED |
| W988 | W30 | 2021 | ART | false | FEED |
(15 more rows not shown)

Query 3:

| author_id | name | work_id | type | year | is_retracted | version_of | institution_id |
|---|---|---|---|---|---|---|---|
| A07 | Priya Ramanathan | W01 | ART | 2021 | false |  | I03 |
| A07 | Priya Ramanathan | W02 | PPR | 2022 | false | W03 | I03 |
| A07 | Priya Ramanathan | W03 | ART | 2023 | false |  | I03 |
| A07 | Priya Ramanathan | W05 | ERR | 2024 | false |  | I03 |
| A07 | Priya Ramanathan | W13 | ART | 2024 | false |  | I03 |
| A07 | Priya Ramanathan | W219 | PPR | 2022 | false | W251 | I03 |
| A07 | Priya Ramanathan | W251 | ART | 2023 | false |  | I03 |
| A07 | Priya Ramanathan | W262 | ART | 2025 | false |  | I03 |
| A07 | Priya Ramanathan | W292 | ART | 2020 | false |  | I03 |
| A07 | Priya Ramanathan | W293 | ART | 2023 | false |  | I03 |
| A07 | Priya Ramanathan | W435 | ART | 2023 | false |  | I03 |
| A07 | Priya Ramanathan | W553 | ART | 2022 | false |  | I03 |
| A07 | Priya Ramanathan | W598 | ART | 2024 | false |  | I03 |
| A07 | Priya Ramanathan | W72 | ART | 2024 | false |  | I03 |
| A07 | Priya Ramanathan | W758 | REV | 2019 | false |  | I03 |
| A07 | Priya Ramanathan | W787 | ART | 2024 | false |  | I03 |
| A07 | Priya Ramanathan | W833 | ART | 2024 | false |  | I03 |
| A07 | Priya Ramanathan | W969 | ART | 2025 | false |  | I03 |
| A09 | Lena Vossberg | W1025 | ART | 2020 | true |  | I07 |
| A09 | Lena Vossberg | W20 | ART | 2024 | false |  | I07 |
| A09 | Lena Vossberg | W203 | ART | 2024 | false |  | I07 |
| A09 | Lena Vossberg | W21 | ART | 2024 | false |  | I07 |
| A09 | Lena Vossberg | W22 | REV | 2024 | false |  | I07 |
| A09 | Lena Vossberg | W23 | ART | 2023 | true |  | I07 |
| A09 | Lena Vossberg | W232 | ART | 2019 | false |  | I07 |
| A09 | Lena Vossberg | W268 | ART | 2025 | false |  | I07 |
| A09 | Lena Vossberg | W366 | ART | 2022 | false |  | I07 |
| A09 | Lena Vossberg | W417 | ART | 2023 | false |  | I07 |
| A09 | Lena Vossberg | W714 | ART | 2025 | false |  | I07 |
| A09 | Lena Vossberg | W738 | ART | 2022 | false |  | I07 |
| A09 | Lena Vossberg | W802 | ART | 2025 | false |  | I07 |
| A09 | Lena Vossberg | W967 | ART | 2022 | false |  | I07 |
| A103 | L. Vossberg | W478 | ART | 2019 | false |  | I08 |
| A103 | L. Vossberg | W478 | ART | 2019 | false |  | I07 |
| A103 | L. Vossberg | W531 | ART | 2023 | false |  | I07 |
| A103 | L. Vossberg | W531 | ART | 2023 | false |  | I08 |
| A103 | L. Vossberg | W686 | ART | 2022 | false |  | I08 |
| A103 | L. Vossberg | W686 | ART | 2022 | false |  | I07 |
| A103 | L. Vossberg | W693 | ART | 2025 | true |  | I07 |
| A108 | Priya Ramanathan | W1018 | ART | 2025 | false |  | I16 |
| A108 | Priya Ramanathan | W437 | EDT | 2024 | false |  | I16 |
| A108 | Priya Ramanathan | W509 | ART | 2024 | false |  | I16 |
| A108 | Priya Ramanathan | W615 | ART | 2023 | false |  | I16 |
| A108 | Priya Ramanathan | W638 | ART | 2021 | false |  | I16 |
| A108 | Priya Ramanathan | W859 | REV | 2023 | false |  | I16 |
| A108 | Priya Ramanathan | W906 | ART | 2024 | false |  | I16 |

Run more queries, or give the final answers.

<invoke name="sql">
SELECT table_name, comment FROM duckdb_tables();
</invoke>

```sql
SELECT table_name, column_name, comment FROM duckdb_columns() WHERE comment IS NOT NULL;
```
```sql
SELECT table_name, comment FROM duckdb_tables() WHERE comment IS NOT NULL;
```
```sql
SELECT * FROM information_schema.tables;
```

Query 1:

(no rows)

Query 2:

(no rows)

Query 3:

| table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_into | is_typed | commit_action | TABLE_COMMENT |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| memory | main | authors | BASE TABLE |  |  |  |  |  | YES | NO |  |  |
| memory | main | authorships | BASE TABLE |  |  |  |  |  | YES | NO |  |  |
| memory | main | citations | BASE TABLE |  |  |  |  |  | YES | NO |  |  |
| memory | main | institutions | BASE TABLE |  |  |  |  |  | YES | NO |  |  |
| memory | main | sources | BASE TABLE |  |  |  |  |  | YES | NO |  |  |
| memory | main | works | BASE TABLE |  |  |  |  |  | YES | NO |  |  |

Query 4:

| table_name | comment |
|---|---|
| authors |  |
| authorships |  |
| citations |  |
| institutions |  |
| sources |  |
| works |  |

Run more queries, or give the final answers.

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

```sql
WITH RECURSIVE r(inst,root) AS (SELECT institution_id, institution_id FROM institutions UNION ALL SELECT i.institution_id, r.root FROM institutions i JOIN r ON i.parent_id=r.inst),
rw AS (SELECT DISTINCT r.root, w.work_id, w.year, w.is_retracted, w.record_source FROM authorships s JOIN r ON r.inst=s.institution_id JOIN works w USING(work_id) WHERE w.type IN ('ART','REV'))
SELECT root, count(*) tot, count(*) FILTER (WHERE is_retracted='false') nonretr, count(*) FILTER (WHERE record_source<>'STG') nostg, count(*) FILTER (WHERE year='2024') y24, count(*) FILTER (WHERE year='2025') y25 FROM rw WHERE root IN ('I01','I02','I06','I07','I08','I09','I14') GROUP BY root ORDER BY root;
```
```sql
WITH RECURSIVE r(inst,root) AS (SELECT institution_id, institution_id FROM institutions UNION ALL SELECT i.institution_id, r.root FROM institutions i JOIN r ON i.parent_id=r.inst),
na AS (SELECT work_id, count(DISTINCT author_id) n FROM authorships GROUP BY 1),
x AS (SELECT DISTINCT s.work_id, s.author_id, r.root FROM authorships s JOIN r ON r.inst=s.institution_id JOIN works w ON w.work_id=s.work_id WHERE w.type IN ('ART','REV') AND w.year='2024' AND r.root IN ('I07','I09'))
SELECT root, count(DISTINCT x.work_id) full_c, sum(1.0/n) frac FROM x JOIN na USING(work_id) GROUP BY root;
```
```sql
WITH na AS (SELECT work_id, count(DISTINCT author_id) n FROM authorships GROUP BY 1),
y AS (SELECT DISTINCT s.work_id, s.author_id, i.country FROM authorships s JOIN institutions i USING(institution_id) JOIN works w ON w.work_id=s.work_id WHERE w.type IN ('ART','REV') AND w.year='2024'),
nc AS (SELECT work_id, author_id, count(*) k FROM y GROUP BY 1,2)
SELECT country, count(DISTINCT work_id) full_c, sum(1.0/n/k) frac FROM y JOIN na USING(work_id) JOIN nc USING(work_id,author_id) WHERE country='GB' GROUP BY 1;
```
```sql
SELECT 'mara_nonresearch' k, string_agg(DISTINCT w.work_id||':'||w.type, ', ') v FROM authorships s JOIN authors a USING(author_id) JOIN works w USING(work_id) WHERE a.orcid='0000-0002-9841-6229' AND w.type NOT IN ('ART','REV')
UNION ALL SELECT 'mara_research', count(DISTINCT w.work_id)::VARCHAR FROM authorships s JOIN authors a USING(author_id) JOIN works w USING(work_id) WHERE a.orcid='0000-0002-9841-6229' AND w.type IN ('ART','REV')
UNION ALL SELECT 'brandt_A05', count(DISTINCT w.work_id)::VARCHAR FROM authorships s JOIN authors a USING(author_id) JOIN works w USING(work_id) WHERE a.orcid='0000-0001-6105-5008' AND w.type IN ('ART','REV')
UNION ALL SELECT 'priya_A07', count(DISTINCT w.work_id)::VARCHAR FROM authorships s JOIN works w USING(work_id) WHERE s.author_id='A07' AND w.type IN ('ART','REV')
UNION ALL SELECT 'voss_works', count(DISTINCT w.work_id)::VARCHAR FROM authorships s JOIN authors a USING(author_id) JOIN works w USING(work_id) WHERE a.orcid='0000-0000-8073-7028' AND w.type IN ('ART','REV')
UNION ALL SELECT 'voss_cites', count(*)::VARCHAR FROM citations WHERE cited_work_id IN (SELECT w.work_id FROM authorships s JOIN authors a USING(author_id) JOIN works w USING(work_id) WHERE a.orcid='0000-0000-8073-7028' AND w.type IN ('ART','REV'))
UNION ALL SELECT 'preprints_with_target', string_agg(work_id,',') FROM works p WHERE version_of IN (SELECT work_id FROM works)
UNION ALL SELECT 'src_S02_2024', count(*)::VARCHAR FROM works WHERE source_id='S02' AND year='2024' AND type IN ('ART','REV')
UNION ALL SELECT 'src_S01', count(*)::VARCHAR FROM works WHERE source_id='S01' AND type IN ('ART','REV')
UNION ALL SELECT 'src_S06', count(*)::VARCHAR FROM works WHERE source_id='S06' AND type IN ('ART','REV')
UNION ALL SELECT 'src_S04', count(*)::VARCHAR FROM works WHERE source_id='S04' AND type IN ('ART','REV');
```
```sql
WITH RECURSIVE r(inst,root) AS (SELECT institution_id, institution_id FROM institutions UNION ALL SELECT i.institution_id, r.root FROM institutions i JOIN r ON i.parent_id=r.inst),
cs AS (SELECT DISTINCT citing_work_id cw FROM citations WHERE cited_work_id IN ('W02','W03')),
ca AS (SELECT s.*, a.orcid, i.country FROM authorships s JOIN authors a USING(author_id) JOIN institutions i USING(institution_id) WHERE work_id IN ('W02','W03')),
cit AS (SELECT s.*, a.orcid, i.country FROM authorships s JOIN authors a USING(author_id) JOIN institutions i USING(institution_id) WHERE work_id IN (SELECT cw FROM cs))
SELECT (SELECT count(*) FROM cs) n,
(SELECT count(DISTINCT work_id) FROM cit WHERE orcid IN (SELECT orcid FROM ca)) self_author,
(SELECT count(DISTINCT work_id) FROM cit WHERE institution_id IN (SELECT inst FROM r WHERE root='I02') AND EXISTS (SELECT 1 FROM ca WHERE institution_id IN (SELECT inst FROM r WHERE root='I02'))) self_I02,
(SELECT count(DISTINCT work_id) FROM cit WHERE institution_id IN (SELECT inst FROM r WHERE root='I01')) self_I01,
(SELECT count(DISTINCT work_id) FROM cit WHERE country IN (SELECT country FROM ca)) self_country,
(SELECT string_agg(DISTINCT country,',') FROM ca) ca_countries;
```
```sql
WITH RECURSIVE r(inst,root) AS (SELECT institution_id, institution_id FROM institutions UNION ALL SELECT i.institution_id, r.root FROM institutions i JOIN r ON i.parent_id=r.inst),
kw AS (SELECT DISTINCT w.work_id, w.year FROM authorships s JOIN r ON r.inst=s.institution_id AND r.root='I07' JOIN works w USING(work_id) WHERE w.type IN ('ART','REV')),
cc AS (SELECT DISTINCT c.citing_work_id, c.cited_work_id FROM citations c JOIN kw ON kw.work_id=c.cited_work_id),
ci AS (SELECT DISTINCT cc.citing_work_id, r.root FROM cc JOIN authorships s ON s.work_id=cc.citing_work_id JOIN r ON r.inst=s.institution_id JOIN institutions i ON i.institution_id=r.root WHERE i.parent_id IS NULL AND r.root<>'I07')
SELECT 'inst' k, root, count(*)::VARCHAR v FROM ci GROUP BY root
UNION ALL SELECT 'top_cited', cited_work_id, count(*)::VARCHAR FROM cc GROUP BY cited_work_id ORDER BY 1, 3 DESC LIMIT 60;
```
```sql
WITH l1 AS (SELECT DISTINCT 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 'l2' k, string_agg(w,',') v FROM l2 UNION ALL SELECT 'two_hop', count(*)::VARCHAR FROM (SELECT w FROM l1 UNION SELECT w FROM l2) WHERE w<>'W01'
UNION ALL SELECT 'W30', count(DISTINCT c.citing_work_id)||' / '||count(DISTINCT c.citing_work_id) FILTER (WHERE y.year BETWEEN '2019' AND '2021') FROM citations c JOIN works y ON y.work_id=c.citing_work_id WHERE c.cited_work_id IN (SELECT work_id FROM works WHERE work_id='W30' OR version_of='W30')
UNION ALL SELECT 'W31fam', count(DISTINCT c.citing_work_id)||' / '||count(DISTINCT c.citing_work_id) FILTER (WHERE y.year BETWEEN '2023' AND '2025') FROM citations c JOIN works y ON y.work_id=c.citing_work_id WHERE c.cited_work_id IN (SELECT work_id FROM works WHERE work_id='W31' OR version_of='W31')
UNION ALL SELECT 'W30fam_versions', string_agg(work_id,',') FROM works WHERE version_of='W30';
```

Query 1:

| root | tot | nonretr | nostg | y24 | y25 |
|---|---|---|---|---|---|
| I01 | 173 | 171 | 173 | 40 | 19 |
| I02 | 118 | 116 | 118 | 29 | 15 |
| I06 | 22 | 22 | 22 | 3 | 3 |
| I07 | 89 | 84 | 70 | 14 | 26 |
| I08 | 25 | 24 | 25 | 6 | 2 |
| I09 | 48 | 47 | 48 | 16 | 4 |
| I14 | 46 | 46 | 46 | 16 | 6 |

Query 2:

| root | full_c | frac |
|---|---|---|
| I07 | 14 | 12.0 |
| I09 | 16 | 4.0 |

Query 3:

| country | full_c | frac |
|---|---|---|
| GB | 48 | 44.0 |

Query 4:

| k | v |
|---|---|
| mara_nonresearch | W04:EDT, W612:PPR, W663:PPR, W271:EDT, W949:ERR, W02:PPR, W813:ERR, W05:ERR, W936:EDT |
| mara_research | 23 |
| brandt_A05 | 9 |
| priya_A07 | 15 |
| voss_works | 18 |
| voss_cites | 42 |
| preprints_with_target | W02,W42,W210,W219,W226,W263,W322,W407,W416,W432,W460,W506,W522,W524,W545,W556,W611,W612,W663,W728,W730,W877,W1049 |
| src_S02_2024 | 27 |
| src_S01 | 97 |
| src_S06 | 26 |
| src_S04 | 13 |

Query 5:

| n | self_author | self_I02 | self_I01 | self_country | ca_countries |
|---|---|---|---|---|---|
| 30 | 7 | 12 | 18 | 21 | GB |

Query 6:

| k | root | v |
|---|---|---|
| inst | I01 | 8 |
| inst | I11 | 5 |
| inst | I09 | 4 |
| inst | I14 | 4 |
| inst | I17 | 3 |
| inst | I18 | 2 |
| inst | I13 | 2 |
| inst | I12 | 16 |
| inst | I28 | 1 |
| inst | I30 | 1 |
| inst | I31 | 1 |
| inst | I16 | 1 |
| inst | I24 | 1 |
| inst | I27 | 1 |
| inst | I15 | 1 |
| inst | I26 | 1 |
| top_cited | W401 | 5 |
| top_cited | W21 | 3 |
| top_cited | W599 | 3 |
| top_cited | W227 | 3 |
| top_cited | W707 | 2 |
| top_cited | W341 | 2 |
| top_cited | W417 | 2 |
| top_cited | W473 | 2 |
| top_cited | W574 | 2 |
| top_cited | W428 | 2 |
| top_cited | W935 | 2 |
| top_cited | W981 | 2 |
| top_cited | W855 | 2 |
| top_cited | W22 | 19 |
| top_cited | W20 | 14 |
| top_cited | W260 | 1 |
| top_cited | W985 | 1 |
| top_cited | W479 | 1 |
| top_cited | W333 | 1 |
| top_cited | W832 | 1 |
| top_cited | W419 | 1 |
| top_cited | W710 | 1 |
| top_cited | W301 | 1 |
| top_cited | W720 | 1 |
| top_cited | W705 | 1 |
| top_cited | W531 | 1 |
| top_cited | W624 | 1 |
| top_cited | W528 | 1 |
| top_cited | W1027 | 1 |
| top_cited | W454 | 1 |
| top_cited | W366 | 1 |
| top_cited | W967 | 1 |
| top_cited | W264 | 1 |
| top_cited | W595 | 1 |
| top_cited | W850 | 1 |
| top_cited | W23 | 1 |
| top_cited | W267 | 1 |

Query 7:

| k | v |
|---|---|
| l2 | W801,W844,W50,W396,W90,W922,W337,W546,W52,W51,W338,W682,W895,W1042 |
| two_hop | 20 |
| W30 | 18 / 2 |
| W31fam | 13 / 13 |
| W30fam_versions |  |

Query 8:

| 1 |
|---|
| 1 |

Run more queries, or give the final answers.

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

```sql
WITH RECURSIVE r(inst,root) AS (SELECT institution_id, institution_id FROM institutions UNION ALL SELECT i.institution_id, r.root FROM institutions i JOIN r ON i.parent_id=r.inst)
SELECT 'author' k, a.author_id||' '||a.name||' '||a.orcid x, count(DISTINCT w.work_id) n FROM authorships s JOIN r ON r.inst=s.institution_id AND r.root='I02' JOIN works w USING(work_id) JOIN authors a USING(author_id) WHERE w.type IN ('ART','REV') GROUP BY ALL
UNION ALL SELECT 'orcid', a.orcid, count(DISTINCT w.work_id) FROM authorships s JOIN r ON r.inst=s.institution_id AND r.root='I02' JOIN works w USING(work_id) JOIN authors a USING(author_id) WHERE w.type IN ('ART','REV') GROUP BY a.orcid
UNION ALL SELECT 'S01_I02', 'x', count(DISTINCT w.work_id) FROM authorships s JOIN r ON r.inst=s.institution_id AND r.root='I02' JOIN works w USING(work_id) WHERE w.type IN ('ART','REV') AND w.source_id='S01'
ORDER BY 1, 3 DESC LIMIT 12;
```

Query 1:

| k | x | n |
|---|---|---|
| S01_I02 | x | 21 |
| author | A07 Priya Ramanathan 0000-0009-2298-3695 | 15 |
| author | A118 Dorian Bellamore 0000-0000-4633-9010 | 14 |
| author | A110 Quinn Yelland 0000-0003-7597-9990 | 14 |
| author | A111 Hester Falconer 0000-0007-5409-8704 | 11 |
| author | A08 Tomasz Wierzba 0000-0007-9318-6032 | 11 |
| author | A125 Greta Ostergaard 0000-0002-5410-0810 | 10 |
| author | A113 Henrik Corbridge 0000-0001-7569-4541 | 9 |
| author | A22 Niamh Corrigan 0000-0009-9065-9397 | 9 |
| author | A123 Florin Penhallow 0000-0008-6186-9535 | 9 |
| author | A114 Ivo Brockmann 0000-0009-9653-9901 | 9 |
| author | A120 Linus Haverkamp 0000-0000-3133-9422 | 8 |

Query 2:

| 1 |
|---|
| 1 |

Run more queries, or give the final answers.

**Q1.** 1. A01, A02 and A03 all carry ORCID 0000-0002-9841-6229.

**Q2.** 32 works, found through every author record with Mara's ORCID (A01, A02, A03, A101):
- A01: W01, W04, W05, W06, W1005, W12, W238, W271, W374, W464, W676, W813, W949
- A02: W02, W07, W1048, W1050, W19, W449, W663, W869, W972
- A03: W03, W206, W763, W817
- A101: W555, W612, W843, W905, W936, W984

**Q3.** No. W37 is by A05 (ORCID …6105-5008) and W38 is by A06 (ORCID …5475-1779).

**Q4.** 9. This counts research works (ART and REV) under ORCID 0000-0001-6105-5008, which covers A05 and A104. A05's own record alone gives 7.

**Q5.** No. W08 is by A04 (ORCID …8662-8360), which differs from Mara's ORCID …9841-6229.

**Q6.** A01 (Mara Ellison). Her 23 research works are all credited to I02, ahead of A07 with 15.

**Q7.** 23 research works (ART and REV) under her ORCID.

**Q8.** I01. The chain is I04 → I03 → I02 → I01.

**Q9.** 118. This counts ART and REV works with any I02-or-descendant affiliation, retractions included.

**Q10.** 173, using the same method for I01.

**Q11.** 89. Of these, 19 come from STG (staging) records.

**Q12.** Full 14, fractional 12.0.

**Q13.** Full: I09 (16 vs 14). Fractional: I07 (12.0 vs 4.0).

**Q14.** Full 48, fractional 44.0.

**Q15.** 27.

**Q16.** 97.

**Q17.** W04, W612, W663, W271, W949, W02, W813, W05, W936. They are EDT, PPR and ERR types, which are not research output.

**Q18.** 15, for A07 only. A108 is a different person with a different ORCID.

**Q19.** 30 distinct works cite W02 or W03.

**Q20.** W02, W42, W210, W219, W226, W263, W322, W407, W416, W432, W460, W506, W522, W524, W545, W556, W611, W612, W663, W728, W730, W877, W1049. Each is a PPR whose `version_of` target is also in the data.

**Q21.** 46.

**Q22.** In total, W30 has more (18 vs 13). In the publication year plus two years after, W31 has more (13 vs 2).

**Q23.** W30 has 18 citations in total and 2 within 2019–2021. W30 has no separate version records.

**Q24.** 18. This combines A09 and A103, which share ORCID …8073-7028.

**Q25.** 42 citations in total to her research works.

**Q26.** Yes. W23 is an ART from I07, and its retraction does not change its type. This depends on whether retracted works are excluded, which the data doesn't say.

**Q27.** I12 (Université de Lorvain), with 16 citing works.

**Q28.** 4.

**Q29.** Author level 7, institution level (I02) 12, country level 21.

**Q30.** 18.

**Q31.** W801, W844, W50, W396, W90, W922, W337, W546, W52, W51, W338, W682, W895, W1042.

**Q32.** 20.

**Q33.** W22, cited by 19 works. W20 is next with 14.

**Q34.** 26.

**Q35.** 25.

**Q36.** 26.

**Q37.** I04. W70 lists I04 first and I06 second.

**Q38.** I06, I04, I03, I02, I01. W71 lists I06 and I04, and I03, I02 and I01 are their ancestors.

**Q39.** 21. These are I02's research works in S01, the only K3 source.

**Q40.** S01 (tier K3).

**Q41.** No. S03 is K1 and S02 is K2.

**Q42.** 13.

**Q43.** Yes. W07 is an ART.

**Q44.** I02, I03, I04, I05, I06, I19, I20, I21.

**Q45.** 22.

**Q46.** I01. I06 sits under I02, which sits under I01.

**Q47.** I09 has more works, but they are multi-author with shares going to other institutions. I07's works are mostly credited to I07 authors, so it leads on fractional counting (12.0 vs 4.0).

**Q48.** The data doesn't support the premise. W70 lists I04, which rolls up through I03 to I02, so it should count as a University of Albury work.

```json
{
"Q1":{"count":1},
"Q2":{"works":["W01","W04","W05","W06","W1005","W12","W238","W271","W374","W464","W676","W813","W949","W02","W07","W1048","W1050","W19","W449","W663","W869","W972","W03","W206","W763","W817","W555","W612","W843","W905","W936","W984"]},
"Q3":{"same":"no"},
"Q4":{"count":9},
"Q5":{"same":"no"},
"Q6":{"author":"A01"},
"Q7":{"count":23},
"Q8":{"parent":"I01"},
"Q9":{"count":118},
"Q10":{"count":173},
"Q11":{"count":89},
"Q12":{"full":14,"fractional":12},
"Q13":{"full":"I09","fractional":"I07"},
"Q14":{"full":48,"fractional":44},
"Q15":{"count":27},
"Q16":{"count":97},
"Q17":{"works":["W04","W612","W663","W271","W949","W02","W813","W05","W936"]},
"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":18},
"Q25":{"count":42},
"Q26":{"counts":"yes"},
"Q27":{"institution":"I12","citing_works":16},
"Q28":{"count":4},
"Q29":{"author":7,"institution":12,"country":21},
"Q30":{"count":18},
"Q31":{"works":["W801","W844","W50","W396","W90","W922","W337","W546","W52","W51","W338","W682","W895","W1042"]},
"Q32":{"count":20},
"Q33":{"work":"W22"},
"Q34":{"count":26},
"Q35":{"count":25},
"Q36":{"count":26},
"Q37":{"institution":"I04"},
"Q38":{"institutions":["I06","I04","I03","I02","I01"]},
"Q39":{"count":21},
"Q40":{"source":"S01"},
"Q41":{"higher":"no"},
"Q42":{"count":13},
"Q43":{"research":"yes"},
"Q44":{"institutions":["I02","I03","I04","I05","I06","I19","I20","I21"]},
"Q45":{"count":22},
"Q46":{"parent":"I01"}
}
```