SQL from zero · Chapter 27 of 37
Saved questions
By the end of this chapter you can save a query under a name, as a view that is always live or a materialized view that is fast, and keep it up to date. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Postgres powers
- 5 tasks
- SQL
- In the all labs plan
The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.
Why you need this
Rohan types WHERE deleted_at IS NULL into almost every query, because members who left must not show up in the numbers. Last week he forgot it once, and sahil, who left in June, appeared on Meera's slide. Meera also wants a home page leaderboard that loads instantly, even though counting posts and likes for every member is work.
With what you know so far, the only way to reuse a query is to copy it. Every copy is a chance to forget the filter, and when the rule changes you have to find and fix every copy. And a heavy query runs again, in full, every time someone opens the home page.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
active-members.sql: a view of the members who have not left
Create a view named active_members: the members who have not left (no date in deleted_at).
It has exactly five columns: id, username, display_name, city and created_at. It must be a view, not a table, so that it stays right when a member leaves or a new one joins.
- Task 2
city-counts.sql: members per city, built on active_members
Create a view named city_counts on top of the active_members view (read from active_members, not from users): each city and how many active members live there. Leave out members with no city.
It has two columns: city and members. It must be a view, so a member who leaves drops out of the counts at once. The Check makes active_members for you first, the same way task 1 does.
- Task 3
leaderboard.sql: a stored leaderboard of posts and likes
Create a materialized view named leaderboard: every active member (not left) who has at least one post that is not deleted, with how many such posts they have and the sum of those posts' likes_count. Deleted posts do not count, and members who left do not appear.
It has three columns: username, posts and total_likes. The active_members view exists when the Check runs this, so you may use it.
- Task 4
refresh.sql: refresh the leaderboard without blocking readers
The leaderboard materialized view exists, but likes have changed since it was made. Bring it up to date without blocking anyone who is reading it: refresh it CONCURRENTLY.
That needs a unique index on the materialized view, so create one first (each username appears once). Afterwards leaderboard has a unique index and its rows match the current posts and likes.
- Task 5 · interview question
Interview: delhi-through-view.sql, Delhi becomes New Delhi through the view
Delhi was renamed in the app. Through the active_members view (update the view, not the users table), set city to 'New Delhi' for every active member whose city is 'Delhi'.
Afterwards the active members who lived in Delhi live in 'New Delhi'. Members who left keep 'Delhi', and no other member changes. The Check makes active_members for you first.
What you will learn
- Views: a query with a name, read like a table, run fresh every time.
- Views on views, and updating through a view: building one saved question on another, and changing rows through one.
- Materialized views: a query whose answer is stored, so reading it is fast, until you refresh it.
- REFRESH ... CONCURRENTLY: bringing the stored answer up to date without making readers wait.
More in Postgres powers
Build "Saved questions" yourself
MingleSphereQL, a social app, needs answers from its data every day. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.