Examples gallery

Lakehouse runtimeDownload PDF (5.6 MB)

The examples use fictional data for Acme Retail, held in these tables of the acme-retail-lake warehouse. Run each statement on its own.

Table Columns
sales.orders order_id, customer_id, store_id, order_ts, order_total, channel, status
sales.order_items order_id, line_no, sku, quantity, unit_price
sales.returns order_id, returned_at, reason
customers.profiles customer_id, name, email, segment, signup_date, country, tags
customers.profile_updates customer_id, name, email, segment, deleted
inventory.products sku, name, category, unit_cost
inventory.stock sku, store_id, on_hand, reorder_point, updated_at

Example 1: Revenue by channel and month#

Group completed orders by month and sales channel; date_trunc turns each timestamp into the first day of its month.

sql
SELECT date_trunc('month', order_ts) AS month,
       channel,
       COUNT(*)                      AS orders,
       ROUND(SUM(order_total), 2)    AS revenue
FROM sales.orders
WHERE status = 'COMPLETE'
GROUP BY 1, 2
ORDER BY month, revenue DESC

Example 2: Top three products in each category#

A window function ranks products inside each category, and an outer query keeps the first three.

sql
WITH product_sales AS (
  SELECT p.category, p.name,
         SUM(i.quantity * i.unit_price) AS revenue
  FROM sales.order_items AS i
  JOIN inventory.products AS p ON p.sku = i.sku
  GROUP BY p.category, p.name
),
ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk
  FROM product_sales
)
SELECT category, name, revenue
FROM ranked
WHERE rnk <= 3
ORDER BY category, rnk

Example 3: Running total and seven-day average#

Daily revenue with a cumulative total and a moving average over the current and six previous days.

sql
WITH daily AS (
  SELECT CAST(order_ts AS DATE) AS day, SUM(order_total) AS revenue
  FROM sales.orders
  WHERE status = 'COMPLETE'
  GROUP BY 1
)
SELECT day,
       revenue,
       SUM(revenue) OVER (ORDER BY day) AS cumulative,
       AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
         AS avg_7_day
FROM daily
ORDER BY day

Example 4: Customers who have never ordered#

An anti join returns the customers with no matching order, a quick list for a win-back campaign.

sql
SELECT c.customer_id, c.name, c.email
FROM customers.profiles AS c
LEFT ANTI JOIN sales.orders AS o
  ON o.customer_id = c.customer_id
WHERE c.signup_date < current_date() - INTERVAL 30 DAYS

Example 5: Low-stock list#

Join stock levels to products and list items at or under their reorder point, with the shortfall.

sql
SELECT p.category, p.name, s.store_id, s.on_hand, s.reorder_point,
       s.reorder_point - s.on_hand AS shortfall
FROM inventory.stock AS s
JOIN inventory.products AS p ON p.sku = s.sku
WHERE s.on_hand <= s.reorder_point
ORDER BY shortfall DESC, p.category

Example 6: Upsert customer changes with MERGE#

Apply a batch of changes: delete flagged customers, update the rest that match and insert the new ones. INSERT * fills every column by name.

sql
MERGE INTO customers.profiles AS t
USING customers.profile_updates AS s
  ON t.customer_id = s.customer_id
WHEN MATCHED AND s.deleted THEN DELETE
WHEN MATCHED THEN UPDATE SET
  t.name = s.name, t.email = s.email, t.segment = s.segment
WHEN NOT MATCHED AND NOT s.deleted THEN INSERT
  (customer_id, name, email, segment)
  VALUES (s.customer_id, s.name, s.email, s.segment)

Example 7: Correct statuses after returns#

Mark orders that appear in the returns table as RETURNED with an UPDATE that uses a subquery.

sql
UPDATE sales.orders
SET status = 'RETURNED'
WHERE status = 'COMPLETE'
  AND order_id IN (SELECT order_id FROM sales.returns)

Example 8: Build a partitioned daily rollup#

Materialise a summary table, partitioned by day, from the orders table. Later queries read this small table instead of scanning orders.

sql
CREATE TABLE sales.daily_channel_revenue USING iceberg
PARTITIONED BY (day)
AS SELECT CAST(order_ts AS DATE) AS day,
          channel,
          COUNT(*)               AS orders,
          SUM(order_total)       AS revenue
   FROM sales.orders
   WHERE status = 'COMPLETE'
   GROUP BY 1, 2

Example 9: Compare today with yesterday using time travel#

Count the rows now and as they were at the start of the day to see what a load added.

sql
SELECT 'now' AS version, COUNT(*) AS orders FROM sales.orders
UNION ALL
SELECT 'start of day', COUNT(*)
FROM sales.orders TIMESTAMP AS OF '2026-10-01 00:00:00'

Example 10: Inspect a table's snapshots and files#

Use the metadata tables to see recent commits and whether the table has many small files.

sql
SELECT snapshot_id, committed_at, operation,
       summary['added-records'] AS added_records
FROM sales.orders.snapshots
ORDER BY committed_at DESC
LIMIT 5
sql
SELECT COUNT(*)                          AS data_files,
       ROUND(AVG(file_size_in_bytes) / 1048576, 1) AS avg_mb
FROM sales.orders.files

Example 11: Pivot channel revenue into columns#

Turn one row per channel into one column per channel for a dashboard.

sql
SELECT * FROM (
  SELECT CAST(order_ts AS DATE) AS day, channel, order_total
  FROM sales.orders
  WHERE status = 'COMPLETE'
)
PIVOT (
  SUM(order_total)
  FOR channel IN ('online', 'in_store', 'mobile_app', 'marketplace')
)
ORDER BY day

Example 12: Evolve a table without rewriting it#

Add a column and change the partitioning. Existing data is untouched, and new rows use the new layout.

sql
ALTER TABLE sales.orders ADD COLUMN coupon_code STRING AFTER status
sql
ALTER TABLE sales.orders REPLACE PARTITION FIELD days(order_ts) WITH months(order_ts)

Example 13: Tags stored as an array#

Explode a customer's tag array to count customers per tag; LATERAL VIEW turns each array element into its own row.

sql
SELECT tag, COUNT(*) AS customers
FROM customers.profiles
LATERAL VIEW explode(tags) t AS tag
GROUP BY tag
ORDER BY customers DESC

Example 14: Safe division and clean text#

ANSI mode raises an error when you divide by zero. try_divide returns NULL instead, and try_cast does the same for a value that is not a number.

sql
SELECT p.sku,
       TRIM(UPPER(p.name))                                  AS name,
       try_divide(SUM(i.unit_price * i.quantity), SUM(i.quantity)) AS avg_price,
       try_cast(p.category AS INT)                          AS category_code
FROM inventory.products AS p
LEFT JOIN sales.order_items AS i ON i.sku = p.sku
GROUP BY p.sku, p.name, p.category

Example 15: Clean up a busy table#

Compact small files, then expire snapshots older than a month while keeping the last five.

sql
CALL system.rewrite_data_files(table => 'sales.orders')
sql
CALL system.expire_snapshots(table => 'sales.orders',
                             older_than => TIMESTAMP '2026-09-01 00:00:00',
                             retain_last => 5)

Example 16: Find slow statements (administrators)#

The audit log shows which statements took longest in the last day.

sql
SELECT timestamp, userId, operation, execution_time_ms / 1000 AS seconds, query
FROM audit.logs
WHERE timestamp >= current_timestamp() - INTERVAL 1 DAY
  AND status = 'SUCCESS'
ORDER BY execution_time_ms DESC
LIMIT 20