Rosy's SQL Practice

Amazon Data & Reporting Analyst Interview Prep

Open SQL Editor Open SQL Worksheet

Login Credentials

  • System: MySQL
  • Server: db
  • Username: rosy
  • Password: (ask Rosy!)
  • Database: practice

OLTP Tables (Normalized)

Transactional tables - the "source of truth" with minimal data duplication.

warehouses

10 rows

Distribution centers

suppliers

8 rows

Product vendors

products

48 rows

SKU catalog

customers

200 rows

Customer records

drivers

116 rows

Delivery drivers

vehicles

153 rows

Fleet vehicles

shipments

~55K rows

Delivery records

shipment_conditions

~220K rows

GPS/IoT time-series

inventory_snapshots

~50K rows

Weekly stock levels

orders

~142K rows

Customer orders

order_items

~424K rows

Order line items

OLAP Tables (Denormalized)

Pre-aggregated reporting tables - faster queries, but derived from OLTP data.

OLAP

daily_delivery_metrics

On-time %, delays, risk by warehouse/day

OLAP

weekly_sales_summary

Revenue, quantity, customers by product/week

OLAP

monthly_inventory_report

Stock levels, reorder alerts by month

Practice Quiz

25 questions covering common SQL interview topics, plus bonus OLTP vs OLAP comparisons!

SELECT & WHERE JOINs GROUP BY Aggregations Time-Series Subqueries CASE Statements Window Functions Period Comparisons

How to Access the Quiz

The quiz is stored as SQL comments in the database. Run this query:

SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'practice';

Or download the quiz file: 005-practice-quiz.sql

Quick Reference

Sample Queries to Try

1. See all tables:

SHOW TABLES;

2. View table structure:

DESCRIBE shipments;

3. Basic JOIN:

SELECT o.id, c.name, o.total_amount FROM orders o JOIN customers c ON o.customer_id = c.id LIMIT 10;

4. Aggregation:

SELECT category, COUNT(*), SUM(unit_cost) FROM products GROUP BY category;

5. Time-series (OLAP):

SELECT report_date, warehouse_name, on_time_count, late_count FROM daily_delivery_metrics WHERE report_date >= '2024-01-01' LIMIT 20;