advancedTop 30 Scenario-Based Questions

You need to store product specifications that vary by category (laptops have RAM/CPU; clothes have size/color). What's the best database design?

Option 1 (JSONB — recommended for PostgreSQL): ALTER TABLE products ADD COLUMN specs JSONB. Store laptop: {ram_gb:16, cpu:'i7', storage_tb:1}. Store clothes: {size:'XL', color:'red', material:'cotton'}. Create GIN index: CREATE INDEX ON products USING GIN(specs). Query: WHERE specs @> '{"ram_gb": 16}'. Pros: flexible, queryable, indexed. Option 2 (EAV — avoid): product_attributes(product_id, key,

Ready to master this question?

Generate a complete walkthrough — background, the full answer in plain language, a working code example explained line by line, a real-world scenario, common mistakes, and how this same question gets asked in different ways.

Next Step

Continue to Write a query to calculate a 3-month moving average and year-to-date cumulative total alongside each monthly sales row. →← Back to all SQL questions