Drill 1 of 3 in Cloud software questions
0

Interview Question Bank · LEFT JOIN · condition in ON · COUNT(column)

1. Active subscriptions and seats per product

EasyCloud softwareQuestion #254

Concept: LEFT JOIN · condition in ON · COUNT(column)show

To list every row of one table with totals from another, start from the full list and LEFT JOIN the details. A filter on the right-hand table belongs in the ON clause: in WHERE it runs after the join and removes the NULL rows that kept the empty products. Count a column from the right table (COUNT(s.id)), because COUNT(*) counts the NULL row as 1, and wrap SUM in COALESCE since SUM over nothing is NULL.

Example (different data):

SELECT t.name, COUNT(m.id) AS active_members,
  COALESCE(SUM(m.hours), 0) AS hours
FROM teams t
LEFT JOIN members m ON m.team_id = t.id AND m.active
GROUP BY t.id, t.name;

Your task

Nimbusworks sells a suite of cloud productivity apps. Finance wants a seat report covering every product in the catalog, including the ones nobody is paying for right now.

For every product in bt_products, return product (its name), active_subscriptions (subscriptions with status 'active') and active_seats (total seats on those subscriptions, 0 if none). Sort by active_seats descending, then product.

Starting Postgres in your browser…

Checking your access…