D2 sql+markdown, run 1: the query session

26 queries3 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.

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

### ontology.md (the vocabulary the data uses)

# Research analytics: a teaching ontology

Works, authors, institutions, sources, authorships, citations and the codes the data uses, each with a definition that states what it means and how to count it. Invented for an experiment; not a model of any real bibliographic database.

## Classes

### Work

A record of something published: an article, review, preprint, editorial, erratum or piece of paratext (work type). Not every work is research output. A research work is a work that (1) is not a test record (see Staging (STG)), (2) is of a research type: an article (ART), a review (REV), a supplement article (SUP) or a preprint (PPR), (3) is not retracted (retracted), and (4) did not appear in a delisted source (Delisted (K0)). Editorials, errata and paratext are never research output. A preprint and its published version (version of) are two records of one research work and count once, under the published version (its year and its source). Every count of works means research works, and citations are counted to research works, unless a question says otherwise.

### Author

An author record: one person as one source spelled them. A person is identified by their ORCID (ORCID), not by their name or record ID. The same person can appear as several author records with differently spelled names: records with the same ORCID are the same person, and every work recorded against any of them is that person's. Two records with the same name and different ORCIDs are different people. A record with no ORCID is a person of its own.

### Institution

An organisation that authors are affiliated with: a university system, university, lab, company, government body or affiliated institute (institution type). Institutions form a hierarchy through parent: a lab can sit under a centre, under a university, under a university system. A work credited to a unit is also credited to every institution above it, to any depth, so counting an institution's works means counting the works of the institution and all its units, each work once. The exception is an affiliated institute (Affiliated institute (AFF)): its parent link does not make it a unit.

### Source

Where a work appeared: a journal or a repository (source type).

### Authorship

One author's place on one work, and one of the institutions they gave for it: one record per author, work and affiliation. An author who gave two affiliations on a work has two authorship records for it, with the same author position and different affiliation sequence. Which one counts is set by affiliation sequence.

### Citation

One work citing another: citing work lists cited work among its references. A citation happens in the citing work's year. A work's citation count is the number of distinct works, of any type, that cite it, leaving out test records (Staging (STG)) and retracted works. A work citing both a preprint and its published version is one citation of that research work. Citation counts for an author, institution or country add up the counts of their research works. When citations are rolled up to the citing side (which institution or country cites another most), each citing work counts once for each institution credited on it, however many of its authors are from that institution. A self-citation depends on the level: an author self-citation is one where the citing and cited works share at least one author (the same person, whatever the record); an institution self-citation is one where they share a credited institution at the level asked about; a country self-citation is one where they share a country of credited institutions. Each level includes the one below it, so the three are different counts. A citation window counts only the citations made in the cited work's publication year and a given number of years after it, so that older and newer works are compared over the same span.

## Properties

### name (CSV column `name`)

The name of an institution, author or source as the record spells it. Names are not identifiers: the same person can be spelled differently, and different people can share a name.

### ROR ID (CSV column `ror`)

The institution's Research Organization Registry identifier (invented here).

### country (CSV column `country`)

ISO code of the country where the institution is. A work's countries are the countries of the institutions credited on it (see affiliation sequence), not of every affiliation listed.

### institution type (CSV column `type`)

What kind of institution it is: a concept of the scheme Institution types.

### parent (CSV column `parent_id`)

The institution this one is a unit of. Follow it upwards, to any depth, to find the institutions a work is also credited to; the ultimate parent is the institution at the top of the chain, the one with no parent. For an affiliated institute (Affiliated institute (AFF)) the link records an agreement, not membership: do not follow it.

### ORCID (CSV column `orcid`)

The person's ORCID identifier (invented here): the identifier that says which person an author record stands for. Same ORCID, same person.

### ISSN (CSV column `issn`)

The journal's ISSN (invented here). Repositories have none.

### source type (CSV column `type`)

Whether the source is a journal or a repository: a concept of the scheme Source types.

### tier (CSV column `tier`)

The source's tier on this dataset's own journal list: a concept of the scheme Journal tiers. The tiers count upwards: K3 is the highest tier and K1 the lowest; K0 is not a tier but a delisting. Repositories have no tier.

### title (CSV column `title`)

The work's title. A preprint and its published version often share a title; two works with the same title are not the same work unless version of links them.

### publication year (CSV column `year`)

The year the work was published. A citation's year is the citing work's publication year.

### work type (CSV column `type`)

What kind of work it is: a concept of the scheme Work types. It decides whether the work is research output (see Work).

### source (CSV column `source_id`)

The journal or repository the work appeared in.

### DOI (CSV column `doi`)

The work's DOI (invented here). A preprint and its published version have different DOIs, though they are one research work.

### version of (CSV column `version_of`)

Set on a preprint whose published version is also in the data: it names that published version. The two records are one research work, counted once, under the published version; the authors, institutions and citations of either record belong to that one work.

### retracted (CSV column `is_retracted`)

True if the work has been retracted. A retracted work is not research output: leave it out of every count of works, and its citations out of every count of citations.

### record source (CSV column `record_source`)

The system the work record was loaded from: a concept of the scheme Record sources.

### work (CSV column `work_id`)

The work the authorship is on.

### author (CSV column `author_id`)

The author record: see Author for which person it stands for.

### author position (CSV column `author_position`)

The author's place in the byline, from 1. The number of a work's authors is the number of distinct people in its byline.

### institution (CSV column `institution_id`)

An institution the author gave on this work.

### affiliation sequence (CSV column `affil_seq`)

The order in which the author listed their affiliations on this work, from 1. House rule: when an author lists several affiliations on one work, the work is credited only to the affiliation listed last, the one with the highest sequence number, for that author; the others are ignored in every count. With one affiliation, that one is credited. Under full counting, a work counts once for each institution credited on it (and for the institutions above it). Under fractional counting, a work is split equally among its authors (distinct people), and each author's share goes to the one institution credited for them, and so to its country; an institution's fractional count adds up the shares of its own authors and its units' authors.

### citing work (CSV column `citing_work_id`)

The work that makes the citation.

### cited work (CSV column `cited_work_id`)

The work that is cited.

## Codes

The codes as they appear in the data, grouped by list.

### Work types

- **ART**, Research article: A peer-reviewed research article. Research output.
- **REV**, Review article: A peer-reviewed article that surveys a field. Research output.
- **SUP**, Supplement article: In this dataset, SUP does not mean supplementary material. It is a full, peer-reviewed research article published in a supplement issue of a journal, such as a proceedings supplement. It is research output and counts exactly like an article (ART).
- **PPR**, Preprint: A version posted to a repository before or without peer review. Research output, but if its published version is in the data (version of), the two are one research work, counted once.
- **EDT**, Editorial: An editor's introduction or opinion piece. Not research output.
- **ERR**, Erratum: A correction notice for an earlier work. Not research output, and not a new version of the work it corrects.
- **PTX**, Paratext: Front matter, issue information, tables of contents and the like: published with a journal issue, but not a contribution. Not research output.

### Institution types

- **SYS**, University system: A group of universities under one governing body. Its works are the works of all its universities and their units.
- **UNI**, University: A university or college.
- **LAB**, Laboratory, centre or institute: A research unit inside its parent institution. Its works are also its parent's works, and its parent's parent's, to the top.
- **AFF**, Affiliated institute: A legally independent institute linked to a university by an agreement. Its parent link records that agreement and nothing more: an affiliated institute is not a unit of its parent, is not part of the parent's system, and its works are never credited to the parent or to anything above it. Its ultimate parent is itself.
- **CO**, Company: A company.
- **GOV**, Government body: A government agency or laboratory.

### Source types

- **JOU**, Journal: A peer-reviewed journal, including its proceedings or supplement issues.
- **REP**, Repository: A preprint server or other repository.

### Journal tiers

- **K3**, Tier K3, the highest: The highest tier: the journals the list ranks first. A higher number is a higher tier, so K3 ranks above K2, and K2 above K1.
- **K2**, Tier K2, the middle: The middle tier: below K3, above K1.
- **K1**, Tier K1, the lowest: The lowest tier that is still listed: below K2.
- **K0**, Delisted source: Not a tier: a source removed from the list for failing its review of editorial practice. Works that appeared in a K0 source are not research output, whatever their type, and are left out of every count of works and citations.

### Record sources

- **FEED**, Bibliographic feed: Loaded from the external bibliographic feed.
- **CRIS**, Institutional research information system: Entered by an institution in its own research information system. As good as a feed record.
- **STG**, Staging load: Test records, loaded into staging to check the import and never removed. They describe no real work: ignore an STG work, its authorships and every citation it makes, in every count.


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

```sql
SELECT * FROM institutions ORDER BY institution_id;
```
```sql
SELECT * FROM sources ORDER BY source_id;
```
```sql
SELECT * FROM authors ORDER BY author_id;
```

Query results

Query 1:

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

Query 2:

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

Query 3:

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

Run more queries, or give the final answers.

The model

<parameter name="sql">SELECT type, record_source, is_retracted, count(*) FROM works GROUP BY ALL ORDER BY ALL;</parameter>
</invoke>
```sql
SELECT * FROM works WHERE version_of IS NOT NULL AND version_of<>'' ORDER BY work_id;
```
```sql
SELECT * FROM works WHERE work_id IN ('W01','W07','W08','W23','W30','W31','W37','W38','W70','W71') OR title ILIKE '%Drift-aware%' ORDER BY work_id;
```

Query results

Query 1:

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

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

Run more queries, or give the final answers.

The model

<parameter name="sql">CREATE OR REPLACE TABLE rw AS SELECT w.work_id, w.year, w.type, w.source_id, w.version_of, coalesce(nullif(w.version_of,''),w.work_id) AS cw FROM works w JOIN sources s USING(source_id) WHERE w.record_source<>'STG' AND w.type IN ('ART','REV','SUP','PPR') AND w.is_retracted='false' AND coalesce(s.tier,'')<>'K0';
CREATE OR REPLACE TABLE rcw AS SELECT DISTINCT cw, (SELECT year FROM works x WHERE x.work_id=rw.cw) y FROM rw WHERE cw IN (SELECT work_id FROM rw);
CREATE OR REPLACE TABLE mp AS SELECT work_id, cw FROM rw WHERE cw IN (SELECT work_id FROM rw);
CREATE OR REPLACE TABLE ppl AS SELECT a.work_id, a.author_id, coalesce(nullif(au.orcid,''),au.author_id) p, a.institution_id, a.affil_seq::INT sq, a.author_position FROM authorships a JOIN authors au USING(author_id);
CREATE OR REPLACE TABLE cred AS SELECT DISTINCT m.cw, p.p, p.institution_id inst FROM ppl p JOIN mp m USING(work_id) WHERE p.sq=(SELECT max(q.sq) FROM ppl q WHERE q.work_id=p.work_id AND q.author_id=p.author_id);
CREATE OR REPLACE TABLE anc AS WITH RECURSIVE r(inst,anc) AS (SELECT institution_id,institution_id FROM institutions UNION ALL SELECT r.inst,i.parent_id FROM r JOIN institutions i ON i.institution_id=r.anc JOIN institutions c ON c.institution_id=r.anc WHERE c.type<>'AFF' AND i.parent_id IS NOT NULL AND i.parent_id<>'') SELECT DISTINCT inst,anc FROM r;
SELECT (SELECT count(*) FROM rcw) nres,(SELECT count(*) FROM anc) nanc;</parameter>
</sql>
```sql
SELECT 'Q9 I02', count(DISTINCT cw) FROM cred JOIN anc USING(inst) WHERE anc='I02' UNION ALL
SELECT 'Q10 I01', count(DISTINCT cw) FROM cred JOIN anc USING(inst) WHERE anc='I01' UNION ALL
SELECT 'Q11 I07', count(DISTINCT cw) FROM cred JOIN anc USING(inst) WHERE anc='I07' UNION ALL
SELECT 'Q21 I14', count(DISTINCT cw) FROM cred JOIN anc USING(inst) WHERE anc='I14' UNION ALL
SELECT 'Q35 I08', count(DISTINCT cw) FROM cred JOIN anc USING(inst) WHERE anc='I08' UNION ALL
SELECT 'Q45 I06', count(DISTINCT cw) FROM cred JOIN anc USING(inst) WHERE anc='I06' UNION ALL
SELECT 'Q34 I07 2025', count(DISTINCT cw) FROM cred JOIN anc USING(inst) JOIN rcw USING(cw) WHERE anc='I07' AND y='2025' UNION ALL
SELECT 'Q12 full', count(DISTINCT cw) FROM cred JOIN anc USING(inst) JOIN rcw USING(cw) WHERE anc='I07' AND y='2024' UNION ALL
SELECT 'Q13 I09 full', count(DISTINCT cw) FROM cred JOIN anc USING(inst) JOIN rcw USING(cw) WHERE anc='I09' AND y='2024' UNION ALL
SELECT 'Q15 S02 2024', count(*) FROM rcw JOIN works ON work_id=cw WHERE source_id='S02' AND year='2024' UNION ALL
SELECT 'Q16 S01', count(*) FROM rcw JOIN works ON work_id=cw WHERE source_id='S01' UNION ALL
SELECT 'Q36 S06', count(*) FROM rcw JOIN works ON work_id=cw WHERE source_id='S06' UNION ALL
SELECT 'Q42 S04', count(*) FROM rcw JOIN works ON work_id=cw WHERE source_id='S04' UNION ALL
SELECT 'Q7 Ellison', count(DISTINCT cw) FROM cred WHERE p='0000-0002-9841-6229' UNION ALL
SELECT 'Q4 A05', count(DISTINCT cw) FROM cred WHERE p='0000-0001-6105-5008' UNION ALL
SELECT 'Q18 A07', count(DISTINCT cw) FROM cred WHERE p='0000-0009-2298-3695' UNION ALL
SELECT 'Q24 A09', count(DISTINCT cw) FROM cred WHERE p='0000-0000-8073-7028' UNION ALL
SELECT 'Q39 I02 K3', count(DISTINCT cw) FROM cred JOIN anc USING(inst) JOIN works ON work_id=cw WHERE anc='I02' AND source_id='S01';
```
```sql
WITH f AS (SELECT cw,count(DISTINCT p) n FROM cred GROUP BY cw), s AS (SELECT c.cw, c.p, count(DISTINCT c.inst) ni FROM cred c GROUP BY 1,2)
SELECT 'frac', a.anc, round(sum(1.0/f.n),4) FROM (SELECT DISTINCT c.cw,c.p,an.anc FROM cred c JOIN anc an ON an.inst=c.inst) a JOIN f USING(cw) JOIN rcw USING(cw) WHERE rcw.y='2024' AND a.anc IN ('I07','I09') GROUP BY a.anc
UNION ALL
SELECT 'GB frac', 'GB', round(sum(1.0/f.n),4) FROM cred c JOIN institutions i ON i.institution_id=c.inst JOIN f USING(cw) JOIN rcw USING(cw) WHERE rcw.y='2024' AND i.country='GB'
UNION ALL
SELECT 'GB full', 'GB', count(DISTINCT cw) FROM cred c JOIN institutions i ON i.institution_id=c.inst JOIN rcw USING(cw) WHERE rcw.y='2024' AND i.country='GB';
```
```sql
SELECT * FROM authorships WHERE work_id IN ('W70','W71','W37','W38','W08','W01','W07','W23') ORDER BY work_id, author_position, affil_seq;
```

Query results

Query 1 failed: Catalog Error: Table with name cred does not exist!

Query 2 failed: Catalog Error: Table with name cred does not exist!

Query 3:

| work_id | author_id | author_position | institution_id | affil_seq |
|---|---|---|---|---|
| W01 | A01 | 1 | I02 | 1 |
| W01 | A07 | 2 | I03 | 1 |
| W07 | A02 | 1 | I02 | 1 |
| W08 | A04 | 1 | I07 | 1 |
| W23 | A09 | 1 | I07 | 1 |
| W37 | A05 | 1 | I05 | 1 |
| W38 | A06 | 1 | I12 | 1 |
| W70 | A08 | 1 | I04 | 1 |
| W70 | A08 | 1 | I06 | 2 |
| W71 | A08 | 1 | I06 | 1 |
| W71 | A08 | 1 | I04 | 2 |

Query 4:

| nres | nanc |
|---|---|
| 712 | 44 |

Run more queries, or give the final answers.

The model

<parameter name="sql">SELECT 1;</parameter>
```sql
SELECT 'Q9 I02', count(DISTINCT cw) FROM cred JOIN anc ON anc.inst=cred.inst WHERE anc='I02' UNION ALL
SELECT 'Q10 I01', count(DISTINCT cw) FROM cred JOIN anc ON anc.inst=cred.inst WHERE anc='I01' UNION ALL
SELECT 'Q11 I07', count(DISTINCT cw) FROM cred JOIN anc ON anc.inst=cred.inst WHERE anc='I07' UNION ALL
SELECT 'Q21 I14', count(DISTINCT cw) FROM cred JOIN anc ON anc.inst=cred.inst WHERE anc='I14' UNION ALL
SELECT 'Q35 I08', count(DISTINCT cw) FROM cred JOIN anc ON anc.inst=cred.inst WHERE anc='I08' UNION ALL
SELECT 'Q45 I06', count(DISTINCT cw) FROM cred JOIN anc ON anc.inst=cred.inst WHERE anc='I06' UNION ALL
SELECT 'Q34 I07 2025', count(DISTINCT cred.cw) FROM cred JOIN anc ON anc.inst=cred.inst JOIN rcw ON rcw.cw=cred.cw WHERE anc='I07' AND y='2025' UNION ALL
SELECT 'Q12 full I07', count(DISTINCT cred.cw) FROM cred JOIN anc ON anc.inst=cred.inst JOIN rcw ON rcw.cw=cred.cw WHERE anc='I07' AND y='2024' UNION ALL
SELECT 'Q13 I09 full', count(DISTINCT cred.cw) FROM cred JOIN anc ON anc.inst=cred.inst JOIN rcw ON rcw.cw=cred.cw WHERE anc='I09' AND y='2024' UNION ALL
SELECT 'Q15 S02 2024', count(*) FROM rcw JOIN works ON work_id=rcw.cw WHERE source_id='S02' AND year='2024' UNION ALL
SELECT 'Q16 S01', count(*) FROM rcw JOIN works ON work_id=rcw.cw WHERE source_id='S01' UNION ALL
SELECT 'Q36 S06', count(*) FROM rcw JOIN works ON work_id=rcw.cw WHERE source_id='S06' UNION ALL
SELECT 'Q42 S04', count(*) FROM rcw JOIN works ON work_id=rcw.cw WHERE source_id='S04' UNION ALL
SELECT 'Q7 Ellison', count(DISTINCT cw) FROM cred WHERE p='0000-0002-9841-6229' UNION ALL
SELECT 'Q4 A05', count(DISTINCT cw) FROM cred WHERE p='0000-0001-6105-5008' UNION ALL
SELECT 'Q18 A07', count(DISTINCT cw) FROM cred WHERE p='0000-0009-2298-3695' UNION ALL
SELECT 'Q24 A09', count(DISTINCT cw) FROM cred WHERE p='0000-0000-8073-7028' UNION ALL
SELECT 'Q39 I02 K3', count(DISTINCT cw) FROM cred JOIN anc ON anc.inst=cred.inst JOIN works ON work_id=cw WHERE anc='I02' AND source_id='S01';
```
```sql
WITH f AS (SELECT cw,count(DISTINCT p) n FROM cred GROUP BY cw)
SELECT 'frac '||a.anc, round(sum(1.0/f.n),4) FROM (SELECT DISTINCT c.cw,c.p,an.anc FROM cred c JOIN anc an ON an.inst=c.inst) a JOIN f ON f.cw=a.cw JOIN rcw ON rcw.cw=a.cw WHERE rcw.y='2024' AND a.anc IN ('I07','I09') GROUP BY a.anc
UNION ALL
SELECT 'GB frac', round(sum(1.0/f.n),4) FROM (SELECT DISTINCT c.cw,c.p FROM cred c JOIN institutions i ON i.institution_id=c.inst WHERE i.country='GB') c JOIN f ON f.cw=c.cw JOIN rcw ON rcw.cw=c.cw WHERE rcw.y='2024'
UNION ALL
SELECT 'GB full', count(DISTINCT c.cw) FROM cred c JOIN institutions i ON i.institution_id=c.inst JOIN rcw ON rcw.cw=c.cw WHERE rcw.y='2024' AND i.country='GB';
```
```sql
SELECT 'Q6', p, count(DISTINCT cw) n FROM cred JOIN anc ON anc.inst=cred.inst WHERE anc='I02' GROUP BY p ORDER BY n DESC LIMIT 3;
```
```sql
SELECT 'Q22/23', cited, count(DISTINCT m2.cw) tot, count(DISTINCT CASE WHEN w.year::INT BETWEEN r.y::INT AND r.y::INT+2 THEN m2.cw END) win
FROM (SELECT c.cited_work_id, c.citing_work_id FROM citations c) c
JOIN (SELECT work_id AS cited, cw FROM mp WHERE cw IN ('W30','W31')) m ON m.cited=c.cited_work_id
JOIN rcw r ON r.cw=m.cw
JOIN works w ON w.work_id=c.citing_work_id AND w.record_source<>'STG' AND w.is_retracted='false'
JOIN sources s ON s.source_id=w.source_id AND coalesce(s.tier,'')<>'K0'
LEFT JOIN (SELECT work_id, coalesce(nullif(version_of,''),work_id) cw FROM works) m2 ON m2.work_id=w.work_id
GROUP BY m.cw;
```

Query results

Query 1:

| 'Q9 I02' | count(DISTINCT cw) |
|---|---|
| Q9 I02 | 95 |
| Q10 I01 | 140 |
| Q11 I07 | 59 |
| Q21 I14 | 45 |
| Q35 I08 | 25 |
| Q45 I06 | 16 |
| Q34 I07 2025 | 7 |
| Q12 full I07 | 13 |
| Q13 I09 full | 16 |
| Q15 S02 2024 | 25 |
| Q16 S01 | 88 |
| Q36 S06 | 36 |
| Q42 S04 | 0 |
| Q7 Ellison | 19 |
| Q4 A05 | 9 |
| Q18 A07 | 15 |
| Q24 A09 | 12 |
| Q39 I02 K3 | 19 |

Query 2:

| ('frac ' || a.anc) | round(sum((1.0 / f.n)), 4) |
|---|---|
| frac I07 | 11.0 |
| frac I09 | 4.0 |
| GB frac | 42.0 |
| GB full | 46.0 |

Query 3:

| 'Q6' | p | n |
|---|---|---|
| Q6 | 0000-0002-9841-6229 | 19 |
| Q6 | 0000-0009-2298-3695 | 15 |
| Q6 | 0000-0000-4633-9010 | 14 |

Query 4 failed: Binder Error: column "cited" must appear in the GROUP BY clause or must be part of an aggregate function.

Query 5:

| 1 |
|---|
| 1 |

Run more queries, or give the final answers.

The model

```sql
CREATE OR REPLACE TABLE cit AS SELECT DISTINCT coalesce(nullif(cw.version_of,''),cw.work_id) cited, c.cited_work_id craw, c.citing_work_id citing, w.year cy FROM citations c JOIN works w ON w.work_id=c.citing_work_id JOIN sources s ON s.source_id=w.source_id JOIN works cw ON cw.work_id=c.cited_work_id WHERE w.record_source<>'STG' AND w.is_retracted='false' AND coalesce(s.tier,'')<>'K0';
CREATE OR REPLACE TABLE credraw AS SELECT DISTINCT p.work_id, p.p, p.institution_id inst FROM ppl p WHERE p.sq=(SELECT max(q.sq) FROM ppl q WHERE q.work_id=p.work_id AND q.author_id=p.author_id);
SELECT 1;
```
```sql
SELECT 'Q22/23' q, cited, count(DISTINCT citing) tot, count(DISTINCT CASE WHEN cy::INT BETWEEN y::INT AND y::INT+2 THEN citing END) win FROM cit JOIN rcw ON rcw.cw=cit.cited WHERE cited IN ('W30','W31') GROUP BY cited;
```
```sql
SELECT 'Q19' q, count(DISTINCT citing) FROM cit WHERE cited='W03';
```
```sql
SELECT 'Q25' q, sum(n) FROM (SELECT cw, (SELECT count(DISTINCT citing) FROM cit WHERE cited=cred.cw) n FROM cred WHERE p='0000-0000-8073-7028' GROUP BY cw);
```
```sql
WITH k AS (SELECT DISTINCT cw FROM cred JOIN anc ON anc.inst=cred.inst WHERE anc='I07'), cc AS (SELECT DISTINCT citing FROM cit JOIN k ON k.cw=cit.cited)
SELECT 'Q27' q, a.anc, count(DISTINCT cr.work_id) n FROM cc JOIN credraw cr ON cr.work_id=cc.citing JOIN anc a ON a.inst=cr.inst GROUP BY a.anc ORDER BY n DESC LIMIT 6;
```
```sql
WITH k AS (SELECT DISTINCT cw FROM cred JOIN anc ON anc.inst=cred.inst WHERE anc='I07'), cc AS (SELECT DISTINCT citing FROM cit JOIN k ON k.cw=cit.cited)
SELECT 'Q28' q, count(DISTINCT cr.work_id) FROM cc JOIN credraw cr ON cr.work_id=cc.citing JOIN anc a ON a.inst=cr.inst WHERE a.anc='I09';
```
```sql
WITH k AS (SELECT cited, count(DISTINCT citing) n FROM cit JOIN (SELECT DISTINCT cw FROM cred JOIN anc ON anc.inst=cred.inst WHERE anc='I07') x ON x.cw=cit.cited GROUP BY cited ORDER BY n DESC, cited LIMIT 4) SELECT 'Q33' q, * FROM k;
```
```sql
WITH cs AS (SELECT DISTINCT citing FROM cit WHERE cited='W03'),
ca AS (SELECT DISTINCT p FROM cred WHERE cw='W03'),
ci2 AS (SELECT DISTINCT an.anc FROM cred JOIN anc an ON an.inst=cred.inst WHERE cw='W03'),
cc AS (SELECT DISTINCT i.country FROM cred JOIN institutions i ON i.institution_id=cred.inst WHERE cw='W03')
SELECT 'Q29/30' q, count(*) tot,
 count(*) FILTER (WHERE EXISTS(SELECT 1 FROM credraw r JOIN ppl pp ON 1=0 WHERE 1=0) OR citing IN (SELECT r.work_id FROM credraw r JOIN ca ON ca.p=r.p)) auth,
 count(*) FILTER (WHERE citing IN (SELECT r.work_id FROM credraw r JOIN anc a ON a.inst=r.inst WHERE a.anc='I02')) i02_citer,
 count(*) FILTER (WHERE citing IN (SELECT r.work_id FROM credraw r JOIN anc a ON a.inst=r.inst JOIN ci2 ON ci2.anc=a.anc)) inst_any,
 count(*) FILTER (WHERE citing IN (SELECT r.work_id FROM credraw r JOIN anc a ON a.inst=r.inst WHERE a.anc='I01')) i01_citer,
 count(*) FILTER (WHERE citing IN (SELECT r.work_id FROM credraw r JOIN institutions i ON i.institution_id=r.inst JOIN cc ON cc.country=i.country)) ctry
FROM cs;
SELECT 'W03 anc', anc FROM (SELECT DISTINCT an.anc FROM cred JOIN anc an ON an.inst=cred.inst WHERE cw='W03');
```
```sql
WITH h1 AS (SELECT DISTINCT citing w FROM cit WHERE cited='W01'),
hs AS (SELECT w FROM h1 UNION SELECT coalesce(nullif(version_of,''),work_id) FROM works WHERE work_id IN (SELECT w FROM h1)),
h2 AS (SELECT DISTINCT citing w FROM cit WHERE craw IN (SELECT w FROM h1) OR cited IN (SELECT w FROM hs))
SELECT 'Q31/32' q, (SELECT count(*) FROM h1) h1n, (SELECT count(*) FROM h2) h2n, (SELECT count(*) FROM (SELECT w FROM h1 UNION SELECT w FROM h2 ) WHERE w<>'W01') u, (SELECT string_agg(w,',' ORDER BY w) FROM h2) h2list;
```
```sql
SELECT 'Q2' q, a.work_id, w.type, w.record_source, w.is_retracted, w.source_id FROM authorships a JOIN works w USING(work_id) WHERE a.author_id IN (SELECT author_id FROM authors WHERE orcid='0000-0002-9841-6229') GROUP BY ALL ORDER BY 2;
```

Query results

Query 1:

| 1 |
|---|
| 1 |

Query 2:

| q | cited | tot | win |
|---|---|---|---|
| Q22/23 | W31 | 11 | 11 |
| Q22/23 | W30 | 16 | 2 |

Query 3:

| q | count(DISTINCT citing) |
|---|---|
| Q19 | 27 |

Query 4:

| q | sum(n) |
|---|---|
| Q25 | 20 |

Query 5:

| q | anc | n |
|---|---|---|
| Q27 | I12 | 12 |
| Q27 | I01 | 5 |
| Q27 | I11 | 4 |
| Q27 | I09 | 4 |
| Q27 | I14 | 3 |
| Q27 | I02 | 3 |

Query 6:

| q | count(DISTINCT cr.work_id) |
|---|---|
| Q28 | 4 |

Query 7:

| q | cited | n |
|---|---|---|
| Q33 | W20 | 13 |
| Q33 | W21 | 2 |
| Q33 | W341 | 2 |
| Q33 | W417 | 2 |

Query 8:

| 'W03 anc' | anc |
|---|---|
| W03 anc | I03 |
| W03 anc | I01 |
| W03 anc | I02 |

Query 9:

| q | h1n | h2n | u | h2list |
|---|---|---|---|---|
| Q31/32 | 7 | 11 | 17 | W1042,W337,W338,W396,W50,W51,W52,W546,W682,W801,W895 |

Query 10:

| q | work_id | type | record_source | is_retracted | source_id |
|---|---|---|---|---|---|
| Q2 | W01 | ART | FEED | false | S01 |
| Q2 | W02 | PPR | FEED | false | S05 |
| Q2 | W03 | ART | FEED | false | S02 |
| Q2 | W04 | EDT | FEED | false | S01 |
| Q2 | W05 | ERR | FEED | false | S02 |
| Q2 | W06 | ART | FEED | false | S03 |
| Q2 | W07 | ART | FEED | false | S04 |
| Q2 | W1005 | ART | FEED | false | S02 |
| Q2 | W1048 | ART | FEED | false | S10 |
| Q2 | W1050 | ART | FEED | false | S09 |
| Q2 | W12 | ART | CRIS | false | S02 |
| Q2 | W19 | ART | CRIS | false | S02 |
| Q2 | W206 | ART | FEED | false | S01 |
| Q2 | W238 | ART | FEED | false | S02 |
| Q2 | W271 | EDT | FEED | false | S01 |
| Q2 | W374 | ART | FEED | true | S02 |
| Q2 | W449 | ART | FEED | false | S04 |
| Q2 | W464 | ART | FEED | false | S09 |
| Q2 | W555 | ART | FEED | false | S09 |
| Q2 | W612 | PPR | FEED | false | S05 |
| Q2 | W663 | PPR | FEED | false | S14 |
| Q2 | W676 | ART | FEED | false | S02 |
| Q2 | W763 | ART | FEED | false | S13 |
| Q2 | W813 | ERR | FEED | false | S02 |
| Q2 | W817 | ART | FEED | false | S10 |
| Q2 | W843 | ART | FEED | false | S02 |
| Q2 | W869 | ART | FEED | false | S02 |
| Q2 | W905 | ART | FEED | false | S01 |
| Q2 | W936 | EDT | FEED | false | S01 |
| Q2 | W949 | ERR | FEED | false | S02 |
| Q2 | W972 | ART | FEED | false | S02 |
| Q2 | W984 | ART | FEED | false | S01 |

Run more queries, or give the final answers.

The model

<invoke name="sql">
<parameter name="sql">WITH cs AS (SELECT DISTINCT citing FROM cit WHERE cited='W03'),
ca AS (SELECT DISTINCT p FROM cred WHERE cw='W03'),
cc AS (SELECT DISTINCT i.country FROM cred JOIN institutions i ON i.institution_id=cred.inst WHERE cw='W03')
SELECT count(*) tot,
 count(*) FILTER (WHERE citing IN (SELECT r.work_id FROM credraw r JOIN ca ON ca.p=r.p)) auth,
 count(*) FILTER (WHERE citing IN (SELECT r.work_id FROM credraw r JOIN anc a ON a.inst=r.inst WHERE a.anc='I02')) i02,
 count(*) FILTER (WHERE citing IN (SELECT r.work_id FROM credraw r JOIN anc a ON a.inst=r.inst WHERE a.anc='I01')) i01,
 count(*) FILTER (WHERE citing IN (SELECT r.work_id FROM credraw r JOIN institutions i ON i.institution_id=r.inst JOIN cc ON cc.country=i.country)) ctry
FROM cs;</parameter>
</invoke>
```

Query results

Query 1:

| tot | auth | i02 | i01 | ctry |
|---|---|---|---|---|
| 27 | 7 | 10 | 14 | 20 |

Run more queries, or give the final answers.

The model

**Q1.** 1. A01, A02, A03 and A101 share ORCID 0000-0002-9841-6229, so they are one person. A04 and A102 are also named M. Ellison but have different ORCIDs.

**Q2.** 32 works: W01, W02, W03, W04, W05, W06, W07, W1005, W1048, W1050, W12, W19, W206, W238, W271, W374, W449, W464, W555, W612, W663, W676, W763, W813, W817, W843, W869, W905, W936, W949, W972, W984. These are the works on any of her four author records, including editorials, errata, a retracted work and works in delisted sources.

**Q3.** No. A05 and A06 have different ORCIDs (…6105-5008 and …5475-1779), so they are different people.

**Q4.** 9. This counts research works on A05 and A104, which share ORCID 0000-0001-6105-5008.

**Q5.** No. W08's author A04 has ORCID …8662-8360, while W01's author A01 has …9841-6229.

**Q6.** A01, with 19 research works credited to I02 and its units. The next highest is A07 with 15.

**Q7.** 19.

**Q8.** I01. I04's parent is I03, then I02, then I01, which has no parent.

**Q9.** 95.

**Q10.** 140.

**Q11.** 59.

**Q12.** Full counting gives 13 and fractional counting gives 11.0.

**Q13.** Under full counting Rostval Technical University (I09) had more, 16 against 13. Under fractional counting Kesting University (I07) had more, 11.0 against 4.0.

**Q14.** Full counting gives 46 and fractional counting gives 42.0.

**Q15.** 25.

**Q16.** 88.

**Q17.** W04, W271 and W936 are editorials, and W05, W813 and W949 are errata. W07, W449 and W763 are also not research output, but because they appeared in delisted sources, not because of their type.

**Q18.** 15.

**Q19.** 27. These are the distinct works citing W03 or its preprint W02, counted once.

**Q20.** W02, W1049, W210, W219, W226, W263, W322, W407, W416, W42, W432, W460, W506, W522, W524, W545, W556, W611, W612, W663, W728, W730, W877. These all have `version_of` set. I did not separately check that each named published version exists in `works`.

**Q21.** 45.

**Q22.** W30 has more in total (16 against 11), and W31 has more within its window (11 against 2).

**Q23.** W30 has 16 citations in total and 2 within its publication year plus two years.

**Q24.** 12.

**Q25.** 20.

**Q26.** No. W23 is retracted, so it is not research output.

**Q27.** I12, with 12 citing works. The next highest is I01 with 5.

**Q28.** 4.

**Q29.** Of the 27 citing works, 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. W01 has 7 direct citers, and these 11 works cite at least one of them.

**Q32.** 17. This is the 7 direct citers plus the second-hop citers, counted once and excluding W01.

**Q33.** W20, with 13 citations. The next highest has 2.

**Q34.** 7.

**Q35.** 25.

**Q36.** 36.

**Q37.** I06. The author of W70 (A08) listed I04 first and I06 last, and only the last-listed affiliation counts.

**Q38.** I04, I03, I02 and I01. On W71 the last-listed affiliation is I04, and credit passes up its chain of parents.

**Q39.** 19.

**Q40.** S01 is the only K3 source.

**Q41.** No. S03 is K1 and S02 is K2, so S03 is lower.

**Q42.** 0. S04 is delisted (K0).

**Q43.** No. W07 appeared in S04, a delisted source.

**Q44.** I02, I03, I04, I19, I05 and I20. The affiliated institutes I06 and I21 are not units of their parents.

**Q45.** 16.

**Q46.** I06. It is an affiliated institute, so its parent link is not followed and it is its own ultimate parent.

**Q47.** Full counting gives each institution one count per work, so Rostval (16) beats Kesting (13). Fractional counting splits each work among its authors, and Rostval's works have many authors credited elsewhere, so its share drops to 4.0 while Kesting keeps 11.0.

**Q48.** W70's credited affiliation is the last one listed, I06, the Halloran Institute. It is an affiliated institute (AFF), so it is not a unit of I02, and the Tessel Laboratory (I04) listed first is ignored.

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