SQL Concepts with Spreadsheets: Joins, Group By & Filters

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Translate SQL WHERE conditions on Harbor & Pine Coffee's September orders into a 1/0 filter flag, COUNTIFS, SUMIFS and an IN list.
• Rank Harbor & Pine Coffee's 12 products by revenue, list the top 3 like ORDER BY … DESC LIMIT 3 and find the slowest seller, all with formulas.
• Build the result of GROUP BY region on Harbor & Pine Coffee's orders as a summary table with COUNTIF, SUMIF and AVERAGEIF, then a two-key GROUP BY region, category cross-table.
• Find Harbor & Pine Coffee's loyal customers, those with at least 3 orders and more than $300 in Q3, the way GROUP BY … HAVING does it.
• Join Harbor & Pine Coffee's October orders to the customer table with XLOOKUP, flag the orders an INNER JOIN would drop and total the joined revenue by segment.
• Keep every November order of Harbor & Pine Coffee with a LEFT JOIN, label missing customers 'Unknown', count the orphans, and find the customers who did not order at all.
• Find the orders that were imported twice in Harbor & Pine Coffee's export, count the distinct orders and customers and total the revenue without the duplicates.
• Bucket Harbor & Pine Coffee's winter orders into Small, Medium and Large with IFS like CASE WHEN, build a year_month key with YEAR and MONTH and group revenue and large orders by month.