Drill 1 of 12 in SaaS questions
0

Interview Question Bank · COALESCE · decimal division

1. Seat utilization

EasySaaSQuestion #109

Concept: COALESCE · decimal divisionshow

Utilization is used divided by available, times 100. Two things go wrong: a NULL anywhere in the math makes the whole result NULL, and dividing two integers throws away the decimals. COALESCE(column, 0) turns a missing count into zero, and writing 100.0 (or casting to numeric) keeps the fraction.

Example (different data):

SELECT room, COALESCE(booked_desks, 0) AS booked,
  ROUND(100.0 * COALESCE(booked_desks, 0) / total_desks, 1) AS occupancy_pct
FROM office_floors;

Your task

At Tallyboard, a dashboard SaaS, customer success wants to see which active accounts are getting value from the seats they pay for, and which are over their limit.

For every active account, return name, seats_used (0 when it is NULL), seats_purchased and utilization_pct (100 * seats_used / seats_purchased, rounded to 1 decimal). Sort by utilization_pct descending, then name.

Starting Postgres in your browser…

Checking your access…