Compute Low,
Filter High

SQL clauses execute in a fixed logical order — not the order you write them:

A value born at step N is invisible to every step before it. That single fact explains every “why can't I…”:

You want to filter on a ratio you computed in SELECT. It doesn't exist yet when WHERE runs, so you move it down a level:

select facid, name,
       monthlymaintenance / nullif(membercost,0) as ratio
from cd.facilities
where ratio > 50   -- ✕ ratio not born yet

with r as (
  select facid, name,
    monthlymaintenance / nullif(membercost,0) as ratio
  from cd.facilities
)
select facid, name from r
where ratio > 50;   -- ✓ ratio is a column now

Same story for a window function — it's born at step 5, so WHERE (step 2) can't filter it. Postgres stops you cold:

select facid, name from cd.facilities
where rank() over (order by monthlymaintenance desc) <= 3;
-- ✕ ERROR: window functions are not allowed in WHERE

Move it into a CTE, filter on the resulting column:

with ranked as (
  select facid, name,
    rank() over (order by monthlymaintenance desc) as rnk
  from cd.facilities
)
select facid, name from ranked where rnk <= 3;

The lookup done right — one table, two roles:

select m.firstname, m.surname,
       r.firstname as rec_first, r.surname as rec_last
from cd.members m
left join cd.members r on r.memid = m.recommendedby
order by m.surname, m.firstname;

The moment a question says “the most / the top / the last,” run this filter:

The ranking family, as one picture

order by joindate desc
limit 1 -- ✕ drops the tied member

where rnk = 1
-- rnk = rank() over
--       (order by joindate desc)
-- ✓ keeps every tied member

This is the tool the pre-window kit can't replace: subquery-with-max, IN, and distinct all work until “per group” or “keep ties” enters the question — then only a window function is clean.

String concatenation & NULL

The same propagation bites when you join a first and last name. The || operator turns the whole result NULL if any piece is null; the concat functions don't:

-- best for names: null-safe, drops the separator for a missing part
select concat_ws(' ', firstname, surname) as full_name
from cd.members;

The error means one thing: GROUP BY collapses rows, so a non-grouped column has many possible values per group and SQL refuses to guess which to show. Every fix answers the same question — what do you actually want that column for?

ERROR: column "b.memid" must appear in the GROUP BY
       clause or be used in an aggregate function

The menu — pick by intent

Grouping by a table's primary key also unlocks its other columns for free (functional dependency) — e.g. group by b.bookid lets you select b.memid ungrouped, because the PK determines it.

Keep every value, or don't collapse at all

-- collect the values instead of picking one
select b.facid, array_agg(b.memid) as members
from cd.bookings b group by b.facid;

-- or don't collapse — a window function keeps every row
select b.memid, b.starttime,
       count(*) over (partition by b.facid) as bookings_here
from cd.bookings b;   -- no GROUP BY, no error

Bug 1 — an unquoted date is arithmetic

where starttime = 2012-09-14
-- ✕ compares to 1989

where starttime = '2012-09-14'
-- ✓ a real date

Bug 2 — timestamp ≠ date

starttime carries a time, so = '2012-09-14' means '2012-09-14 00:00:00' — it matches only bookings at midnight. To get every booking on that day, use a half-open range:

select facid, starttime
from cd.bookings
where starttime >= '2012-09-14'
  and starttime <  '2012-09-15'   -- the day, up to (not incl.) the next
order by starttime;

The toolkit

current_date                       -- today
now()  /  current_timestamp        -- this instant
starttime + interval '1 day'       -- date math
extract(hour from starttime)       -- pull a piece out
date_trunc('day', starttime)        -- floor to midnight
to_char(starttime, 'YYYY-MM-DD')   -- format for display

1 · Drill partition by + the rank family to reflex

Small action: memorise the 1-2-3 / 1-1-3 / 1-1-2 picture; write ~10 top-N-per-group queries. Outsized result: it converts the entire pre-window toolkit from “works sometimes” to “works always,” and it's the pattern these problems are actually testing. Do first.

2 · A 10-second pre-submit checklist

Small action: before saying “done,” check — (1) ORDER BY on the outermost query, spec columns in exact order? (2) commas between CTEs, none before the final SELECT? (3) honoured every spec word — “(s),” “if any,” “no duplicates”? Outsized result: it kills the entire class of last-mile bugs — a habit, not new knowledge.

3 · Install one reflex: “is this a self-join?”

Small action: before writing a CTE, ask “am I just looking up related rows?” → alias the table twice instead. Outsized result: corrects the recurring over-build; fewer layers read as cleaner modeling and leave fewer places for bugs.

To test whether a column (or set of columns) is the grain, group by it and look for any group holding more than one row. Zero rows back means it's unique — that column set is the grain (or finer):

select memid, count(*)
from cd.members
group by memid
having count(*) > 1;   -- 0 rows → memid IS the grain

The task: list members who have used a tennis court, with the court name and the member's name as a single column, no duplicates, ordered by member then facility.

Approach A — dedup first (GROUP BY in a CTE)

with tennis as (
  select f.facid, b.memid, f.name
  from cd.facilities f
  join cd.bookings b on b.facid = f.facid
  where f.name like 'Tennis%'
  group by f.facid, b.memid    -- one row per (court, member); name via PK dependency
)
select concat_ws(' ', m.firstname, m.surname) as member, t.name as facility
from cd.members m
join tennis t on t.memid = m.memid
order by member, facility;

Approach B — dedup last (triple join + DISTINCT)

select distinct concat_ws(' ', m.firstname, m.surname) as member,
                f.name as facility
from cd.members m
join cd.bookings   b on b.memid = m.memid
join cd.facilities f on f.facid = b.facid
where f.name like 'Tennis%'
order by member, facility;