Which SQL construct would correctly include categories with zero revenue when calculating total revenue by product category for the last calendar quarter in PostgreSQL?

Which SQL construct would correctly include categories with zero revenue when calculating total revenue by product category for the last calendar quarter in PostgreSQL?

• A. Use INNER JOIN between products and sales with a filter on sale_date in the WHERE clause
• B. Use LEFT JOIN from products to sales and COALESCE(SUM(s.revenue), 0) with the date filter in the JOIN condition
• C. Use FULL OUTER JOIN and then SUM over both sides
• D. Use CROSS JOIN and filter to only revenue rows

Answer: (B)

When you want to show categories even if there’s no revenue in the period, you need to preserve every category row in the result. A left join from categories (or products mapped to categories) to sales keeps all categories, attaching only matching sales data. If there are no sales in the last calendar quarter for a category, the joined revenue values are NULL, but the category row still exists. Applying the date filter in the join condition ensures you limit the joined sales to the last quarter without dropping the category rows that have no matching sales. If you put that date filter in a WHERE clause, those categories would be filtered out entirely, and you’d lose zero-revenue categories. Then aggregate with COALESCE(SUM(s.revenue), 0). The sum over the joined revenue values gives the total for each category; for categories with no sales in the period, the sum would be NULL, and COALESCE converts that to 0, yielding the correct zero-revenue result. Other patterns are less suitable: an inner join would lose categories with no matching sales, a full outer join adds unnecessary complexity, and a cross join would explode the result with a Cartesian product.

When you want to show categories even if there’s no revenue in the period, you need to preserve every category row in the result. A left join from categories (or products mapped to categories) to sales keeps all categories, attaching only matching sales data. If there are no sales in the last calendar quarter for a category, the joined revenue values are NULL, but the category row still exists.

Applying the date filter in the join condition ensures you limit the joined sales to the last quarter without dropping the category rows that have no matching sales. If you put that date filter in a WHERE clause, those categories would be filtered out entirely, and you’d lose zero-revenue categories.

Then aggregate with COALESCE(SUM(s.revenue), 0). The sum over the joined revenue values gives the total for each category; for categories with no sales in the period, the sum would be NULL, and COALESCE converts that to 0, yielding the correct zero-revenue result.

Other patterns are less suitable: an inner join would lose categories with no matching sales, a full outer join adds unnecessary complexity, and a cross join would explode the result with a Cartesian product.