Query syntax

These examples target FastBI Lake. The sample lake.uploads.orders table has order_id, customer_id, order_date, country, status, and numeric revenue columns. Adapt them to your schema.

Select and filter

Choose the fields you need. Use a half-open date range to include the start and exclude the next period’s start.

SELECT order_id, customer_id, revenue
FROM lake.uploads.orders
WHERE order_date >= DATE '2026-09-01'
  AND order_date < DATE '2026-10-01'
  AND status = 'completed'
ORDER BY revenue DESC, order_id
LIMIT 100;

For timestamp columns, use timestamp boundaries appropriate to your reporting timezone instead of assuming that a calendar date represents the same instant everywhere.

Group and aggregate

WHERE filters input rows. HAVING filters grouped results. Every selected expression that is not aggregated should appear in GROUP BY.

SELECT country,
       COUNT(*) AS orders,
       SUM(revenue) AS revenue,
       AVG(revenue) AS average_order_value
FROM lake.uploads.orders
WHERE status = 'completed'
GROUP BY country
HAVING COUNT(*) >= 10
ORDER BY revenue DESC
LIMIT 100;

This assumes one row per order. On an order-line table, count distinct order IDs and account for how revenue is represented.

Join tables

Use INNER JOIN for matching rows and LEFT JOIN to retain all rows from the left-hand table.

SELECT o.order_id, c.segment, o.revenue
FROM lake.uploads.orders AS o
LEFT JOIN lake.uploads.customers AS c
  ON o.customer_id = c.customer_id
LIMIT 100;

The example assumes customers.customer_id is unique. If it is not, each matching customer row duplicates an order in the result and can inflate totals.

Common table expressions

Use WITH to name intermediate results and make complex queries easier to review.

WITH customer_revenue AS (
  SELECT customer_id, SUM(revenue) AS revenue
  FROM lake.uploads.orders
  WHERE status = 'completed'
  GROUP BY customer_id
)
SELECT customer_id, revenue
FROM customer_revenue
WHERE revenue > 1000
ORDER BY revenue DESC
LIMIT 100;

Combine compatible results

UNION ALL keeps all rows from both queries; UNION removes duplicate result rows. Align column positions and compatible types. Add a source label when you need to preserve provenance.

SELECT 'web' AS source, order_id, revenue
FROM lake.uploads.web_orders
UNION ALL
SELECT 'app' AS source, order_id, revenue
FROM lake.uploads.app_orders
LIMIT 100;

The final limit applies to the combined result. Add ordering when the selection must be deterministic.

Continue with the SQL syntax reference.