The public shelf

Write a SQL query that answers my question.

Database: {{database:PostgreSQL}}
My question: {{question}}

Tables and columns (paste the schema, or describe the tables, their columns, and how they relate):
{{schema}}

Give me:
1. The query, formatted, with a short comment above any part that is not obvious.
2. A plain-English walkthrough: which tables it reads, how they are joined, what is filtered out, and how the result is grouped and sorted.
3. Every assumption you made. Common ones: what counts as "active" or "recent", how nulls are treated, whether time ranges include their end date, which time zone dates are in, and whether rows can be duplicated by a join.
4. What the output will look like: the columns, and one or two example rows.
5. A quick check I can run to confirm that the number is right, such as a count before and after the join.

Rules:
- Use only tables and columns from the schema I gave. If something I need is not there, say what is missing; do not make up a column.
- Use syntax that works in the database I named.
- If my question could mean two different things, write both queries and explain what distinguishes them.
- Watch for joins that multiply rows and inflate sums or counts. Call it out if that is a risk here.
- If the query could be slow on a large table, say which index would help.
- This is for reading data. Do not write anything that changes or deletes data unless I ask for it.

Help me choose between these options.

The decision: {{decision}}
The options: {{options}}
What matters most to me, most important first: {{priorities}}
Hard limits (budget, deadline, must-haves): {{constraints:none}}

Do this:
1. Restate my priorities as 4 to 6 criteria and give each a weight that reflects the order I gave. Show the weights.
2. Build a comparison table: one row per criterion, one column per option. Use specifics (numbers, features, terms), not "good" or "better".
3. Score each option on each criterion from 1 to 5, with a one-line reason for each score.
4. Rule out any option that breaks a hard limit, and say which limit.
5. Recommend one option. Explain the recommendation in terms of my priorities, not the total score alone.
6. Say what would change the recommendation: the one or two facts that, if different, would make another option the better choice.

Rules:
- Use only the information I gave you plus general knowledge you are confident of. Mark anything you are unsure about as "verify".
- Do not quote prices, specifications, or reviews as current facts; tell me to check them.
- If two options are close, say so plainly. Do not manufacture a clear winner.
- If I am missing an obvious option, mention it in one line at the end.

Details about each option:
{{details}}