SQL LEAST

The mirror of GREATEST — returns the smallest value out of a list, useful for applying a ceiling instead of a floor.

What & Why

LEAST(value1, value2, ...) returns the smallest of its arguments — the same row-by-row comparison as GREATEST, just the opposite direction. Where GREATEST enforces a floor, LEAST enforces a ceiling.

See How It Works

BUSINESS QUESTION

Marketing wants reported clicks capped at the number of opens.

id	campaign_id	sent_at	recipients	opens	clicks	unsubscribes	bounces
201	1	2024-01-20 15:00:00+00	1200	540	180	9	24
202	2	2024-02-15 16:30:00+00	800	420	96	5	12
203	3	2024-03-18 13:00:00+00	1500	610	225	14	31
204	4	2024-04-08 14:00:00+00	0	0	0	0	0

EXAMPLE QUERY

SELECT
  id AS send_id,
  opens,
  clicks,
  LEAST(clicks, opens) AS valid_clicks
FROM marketing.email_sends
ORDER BY send_id;

RESULT — exact output from the displayed Queryflo rows

send_id	opens	clicks	valid_clicks
201	540	180	180
202	420	96	96
203	610	225	225
204	0	0	0

The valid click count is the smaller value on every email-send row.

Now You Try

Practice this concept

Marketing wants reported clicks capped at the number of opens.

Available schema

marketing

Prefix tables with marketing.table_name.

send_idopensclicksvalid_clicks

marketing.email_sends

Column	Type
id	integer
campaign_id	integer
sent_at	timestamp with time zone
recipients	integer
opens	integer
clicks	integer
unsubscribes	integer
bounces	integer
legacy_send_group	text

query.sql

Intermediate business practice