Level 8 · Boss: import, clean and publish

This boss runs a small import end to end: the kind of job where a supplier’s list joins your catalog and someone downstream relies on the result. Because every run starts fresh, each step repeats the earlier steps’ statements (the starters include them) and adds its own.

Step 1: load and profile

An INSERT can take its rows from a query instead of a VALUES list:

INSERT INTO lighting_feed
SELECT product_id, name, category, unit_cost, unit_price
FROM products
WHERE category = 'Lighting';

The columns are matched by position, so the SELECT must list them in the table’s order. The supplier’s three rows follow as VALUES.

Then profile before you clean. Grouping by the raw category shows what actually arrived: 21 rows say Lighting, one says LIGHTING and one says ' lighting ' with spaces. Counting unit_cost and list_price per group shows one row without a cost (the Calder Arc Floor Lamp) and one without a price (the Wren Ceramic Table Lamp). A profile like this is your list of cleaning jobs.

Step 2: clean with UPDATE and DELETE

UPDATE lighting_feed SET category = 'Lighting' WHERE LOWER(TRIM(category)) = 'lighting';
DELETE FROM lighting_feed WHERE list_price IS NULL;

The UPDATE’s WHERE normalizes before comparing, so one condition catches every spelling. The DELETE removes the product that cannot be sold yet. The product without a cost stays: it has a price, so it belongs in the catalog, and its margin is simply unknown. Deleting it would also understate the premium tier. The cleaned feed has 22 rows.

Step 3: publish a summary with a check

CREATE TABLE lighting_summary AS
SELECT CASE WHEN list_price >= 300 THEN 'premium' ELSE 'core' END AS tier,
       COUNT(*) AS products,
       COUNT(unit_cost) AS with_cost,
       ROUND(AVG(list_price), 2) AS avg_price,
       ROUND(100 * AVG((list_price - unit_cost) / list_price), 1) AS avg_margin_pct
FROM lighting_feed
GROUP BY 1;

AVG skips NULLs, so the margin average covers the 5 premium products that have a cost while products still counts all 6. Showing with_cost next to the margin tells the reader how many rows the margin rests on. Core products average $135.81 with a 56.4% margin; premium ones average $407.33 with 53.9%.

The final query adds rows_match: the summary’s product counts must add up to the feed. If a tier CASE had a gap (say > 300 and < 300, losing a product at exactly $300), the check would turn false.

Common mistakes

• Listing INSERT … SELECT columns in a different order from the table, which silently puts names in the category column or costs in the price column.
• Cleaning before profiling, and fixing only the spellings you happened to notice.
• Deleting rows with a NULL cost: they are real products.
• Publishing a summary without a check that it covers every row.

In SQL Server

INSERT INTO ... SELECT and UPDATE/DELETE are the same in T-SQL. The summary would be built with SELECT ... INTO dbo.LightingSummary FROM ..., and the check written as a CASE WHEN ... THEN 1 ELSE 0 END column, since T-SQL has no boolean column type. A real supplier file would come in through the SSMS Import Flat File wizard; in DuckDB it is INSERT INTO lighting_feed SELECT * FROM read_csv('feed.csv').