OLTP systems optimize frequent business transactions, while OLAP systems optimize analytical scans and aggregation.
-- Apply OLTP vs OLAP to a small, testable schema.\nSELECT * FROM orders LIMIT 10;Use the smallest correct statement, test it with representative data, and verify constraints and performance before production use.
A retailer copies operational sales data into an analytics model so analysts can compare revenue by date, store, product, and customer segment.
SELECT d.calendar_month, p.category, SUM(f.amount) AS revenue\nFROM sales_fact f\nJOIN date_dim d ON d.id = f.date_id\nJOIN product_dim p ON p.id = f.product_id\nGROUP BY d.calendar_month, p.category\nORDER BY d.calendar_month, revenue DESC;The schema or operation satisfies the stated rule, rejects invalid data, and can be verified with a repeatable query.
This lesson reuses picostore. Relevant tables: orders, order_items, products, customers. Keep the starter rows from the Introduction lesson so results remain comparable.
SELECT o.order_id, c.name AS customer, o.status, o.total\nFROM orders o\nJOIN customers c ON c.customer_id = o.customer_id\nWHERE o.total >= 1000\nORDER BY o.ordered_at DESC;Run the verification query, compare the returned rows with the starter data, and explain why every included or excluded row is correct.
Start with a small customer table and retrieve active customers in a predictable order.
SELECT customer_id, name, email\nFROM customers\nWHERE status = 'active'\nORDER BY name;Use a CTE and a window function to rank customer revenue while keeping the query readable and testable.
WITH customer_revenue AS (\n SELECT customer_id, SUM(total_amount) AS revenue\n FROM orders\n WHERE order_status = 'completed'\n GROUP BY customer_id\n)\nSELECT customer_id, revenue,\n DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank\nFROM customer_revenue\nORDER BY revenue_rank, customer_id;The architecture applies to both engines, but replication, failover, materialized-view, and warehouse tooling is vendor-specific. Define consistency, recovery, and freshness requirements before choosing an implementation.
OLTP systems optimize frequent business transactions, while OLAP systems optimize analytical scans and aggregation.
-- Apply OLTP vs OLAP to a small, testable schema.\nSELECT * FROM orders LIMIT 10;Use the smallest correct statement, test it with representative data, and verify constraints and performance before production use.
A retailer copies operational sales data into an analytics model so analysts can compare revenue by date, store, product, and customer segment.
SELECT d.calendar_month, p.category, SUM(f.amount) AS revenue\nFROM sales_fact f\nJOIN date_dim d ON d.id = f.date_id\nJOIN product_dim p ON p.id = f.product_id\nGROUP BY d.calendar_month, p.category\nORDER BY d.calendar_month, revenue DESC;The schema or operation satisfies the stated rule, rejects invalid data, and can be verified with a repeatable query.
This lesson reuses picostore. Relevant tables: orders, order_items, products, customers. Keep the starter rows from the Introduction lesson so results remain comparable.
SELECT o.order_id, c.name AS customer, o.status, o.total\nFROM orders o\nJOIN customers c ON c.customer_id = o.customer_id\nWHERE o.total >= 1000\nORDER BY o.ordered_at DESC;Run the verification query, compare the returned rows with the starter data, and explain why every included or excluded row is correct.
Start with a small customer table and retrieve active customers in a predictable order.
SELECT customer_id, name, email\nFROM customers\nWHERE status = 'active'\nORDER BY name;Use a CTE and a window function to rank customer revenue while keeping the query readable and testable.
WITH customer_revenue AS (\n SELECT customer_id, SUM(total_amount) AS revenue\n FROM orders\n WHERE order_status = 'completed'\n GROUP BY customer_id\n)\nSELECT customer_id, revenue,\n DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank\nFROM customer_revenue\nORDER BY revenue_rank, customer_id;The architecture applies to both engines, but replication, failover, materialized-view, and warehouse tooling is vendor-specific. Define consistency, recovery, and freshness requirements before choosing an implementation.