Examples¶
Example SemQL queries for common analytical scenarios, ordered from simple to advanced. The examples use illustrative Models such as customers, orders and sales. Replace the Model, Dimension, Measure and Filter names with the ones in your own semantic layer.
Some constructs aren't available on every database. See Compilation & Dialects for the per-database list.
Simple queries¶
List all records from a model¶
SELECT name, email FROM customers
Filter with a predefined filter¶
SELECT name, email
FROM customers[enterprise]
Filter with a WHERE clause¶
SELECT name, region, created_at
FROM customers
WHERE region = 'Europe'
ORDER BY created_at DESC
Use a measure (no aggregation in the query needed)¶
SELECT name, total_revenue
FROM customers
GROUP BY name
ORDER BY total_revenue DESC
LIMIT 10
Filtering and aggregation¶
Revenue by region, for a specific year¶
SELECT region, total_revenue
FROM sales
WHERE year = 2024
GROUP BY region
ORDER BY total_revenue DESC
Top products by order count, last 30 days¶
SELECT product_name, order_count
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY product_name
ORDER BY order_count DESC
LIMIT 20
Active organizations with more than 5 users¶
SELECT name, user_count
FROM organizations[active_only]
GROUP BY name
HAVING user_count > 5
ORDER BY user_count DESC
Revenue by region, without GROUP BY¶
When a query aggregates and has no GROUP BY, SemQL groups by every non-aggregated column in SELECT. This query is grouped by region and status:
SELECT region, status, total_revenue
FROM orders
ORDER BY total_revenue DESC
LIMIT 5
GROUP BY ALL does the same thing:
SELECT region, status, total_revenue
FROM orders
GROUP BY ALL
ORDER BY total_revenue DESC
LIMIT 5
Completed vs. all orders per region (conditional aggregate)¶
SELECT
region,
COUNT(*) AS orders_placed,
COUNT(*) FILTER (WHERE status = 'completed') AS completed_orders
FROM orders
Median order amount per region¶
SELECT
region,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_order_amount
FROM orders
Note
PERCENTILE_CONT and PERCENTILE_DISC work on PostgreSQL, Amazon Redshift, Snowflake, Databricks, MotherDuck and uploaded files. Other databases refuse them with an error.
Monthly revenue trend¶
SELECT
DATE_TRUNC('month', order_date) AS month,
total_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month ASC
Joins¶
Organizations with their user counts¶
SELECT
organizations.name,
organizations.industry,
COUNT(users.user_id) AS user_count
FROM organizations[active_only]
LEFT JOIN users ON organizations.organization_id = users.organization_id
GROUP BY organizations.name, organizations.industry
ORDER BY user_count DESC
Orders with customer details¶
SELECT
orders.order_id,
customers.name AS customer_name,
customers.region,
orders.total_amount,
orders.created_at
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
WHERE orders.created_at >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY orders.created_at DESC
Revenue per account manager (three-way join)¶
SELECT
users.full_name AS account_manager,
customers.region,
total_revenue
FROM sales
JOIN customers ON sales.customer_id = customers.customer_id
JOIN users ON customers.account_manager_id = users.user_id
WHERE sales.year = 2024
GROUP BY users.full_name, customers.region
ORDER BY total_revenue DESC
Avoiding inflated totals across a join (fan-out)¶
If one customer has many orders, joining customers to orders repeats each customer row once per order. When you join on the keys of a relationship between the Models, SemQL refuses a SUM, COUNT or AVG over the repeated Model, including Measures defined with them. A Measure defined with COUNT(DISTINCT …), MIN or MAX isn't refused:
-- REFUSED when customer_count is defined with COUNT(customer_id), SUM or AVG:
-- it would be inflated by the join
SELECT customers.region, customer_count, order_count
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
Aggregate each Model on its own first, then join the results:
WITH customers_by_region AS (
SELECT region, customer_count
FROM customers
),
orders_by_region AS (
SELECT customers.region, order_count
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
)
SELECT c.region, c.customer_count, o.order_count
FROM customers_by_region c
JOIN orders_by_region o ON c.region = o.region
Or count distinct values, which repeated rows don't change:
SELECT
customers.region,
COUNT(DISTINCT customers.customer_id) AS customers,
order_count
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
Window functions¶
Note
Window functions aren't available on ZenDB. On Actian, Ingres and Informix, aggregate windows with ORDER BY inside OVER (such as the running total below) are rejected; ranking functions such as RANK() and LAG() still work.
Rank customers by revenue within each region¶
SELECT
customer_name,
region,
total_revenue,
RANK() OVER (PARTITION BY region ORDER BY total_revenue DESC) AS rank_in_region
FROM customers
GROUP BY customer_name, region
ORDER BY region, rank_in_region
Month-over-month revenue change¶
SELECT
DATE_TRUNC('month', order_date) AS month,
total_revenue,
LAG(total_revenue) OVER (ORDER BY DATE_TRUNC('month', order_date)) AS prev_month_revenue,
total_revenue - LAG(total_revenue) OVER (ORDER BY DATE_TRUNC('month', order_date)) AS change,
ROUND(
100.0 * (total_revenue - LAG(total_revenue) OVER (ORDER BY DATE_TRUNC('month', order_date)))
/ NULLIF(LAG(total_revenue) OVER (ORDER BY DATE_TRUNC('month', order_date)), 0),
1
) AS change_pct
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month
Running total of revenue¶
SELECT
order_date,
total_revenue AS daily_revenue,
SUM(total_revenue) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_revenue
FROM orders
GROUP BY order_date
ORDER BY order_date
CTEs¶
Note
ZenDB doesn't support CTEs (WITH). Rewrite them as inline subqueries.
Two-step analysis: find top regions, then show their details¶
WITH top_regions AS (
SELECT region, total_revenue
FROM sales
WHERE year = 2024
GROUP BY region
ORDER BY total_revenue DESC
LIMIT 5
)
SELECT
customers.name,
customers.region,
customers.total_revenue AS customer_revenue
FROM customers
JOIN top_regions ON customers.region = top_regions.region
GROUP BY customers.name, customers.region, top_regions.total_revenue
ORDER BY top_regions.total_revenue DESC, customer_revenue DESC
Cohort comparison using CTEs¶
WITH new_customers AS (
SELECT customer_id, total_revenue
FROM customers
WHERE DATE_TRUNC('year', created_at) = '2024-01-01'
GROUP BY customer_id
),
returning_customers AS (
SELECT customer_id, total_revenue
FROM customers
WHERE DATE_TRUNC('year', created_at) < '2024-01-01'
AND last_order_date >= '2024-01-01'
GROUP BY customer_id
)
SELECT 'New (2024)' AS cohort, COUNT(*) AS count, SUM(total_revenue) AS revenue
FROM new_customers
UNION ALL
SELECT 'Returning' AS cohort, COUNT(*) AS count, SUM(total_revenue) AS revenue
FROM returning_customers
UNION, INTERSECT and EXCEPT¶
Compare two time periods side by side¶
SELECT
'Q1 2024' AS period,
product_category,
total_revenue
FROM sales
WHERE year = 2024 AND quarter = 1
GROUP BY product_category
UNION ALL
SELECT
'Q1 2023' AS period,
product_category,
total_revenue
FROM sales
WHERE year = 2023 AND quarter = 1
GROUP BY product_category
ORDER BY product_category, period
Customers who ordered in both years (INTERSECT)¶
SELECT customer_id FROM orders WHERE year = 2024
INTERSECT
SELECT customer_id FROM orders WHERE year = 2025
Use EXCEPT instead of INTERSECT to find customers who ordered in 2024 but not in 2025. INTERSECT and EXCEPT aren't available on ZenDB.
Combine two segments into a single report¶
SELECT 'Enterprise' AS segment, name, total_revenue
FROM customers[enterprise]
GROUP BY name
UNION ALL
SELECT 'SMB' AS segment, name, total_revenue
FROM customers[smb]
GROUP BY name
ORDER BY total_revenue DESC
Geo functions¶
Stores within 50 km of a city, sorted by distance¶
SELECT
store_name,
city,
GEO_DISTANCE(51.5074, -0.1278, store_lat, store_lng) AS distance_from_london_km
FROM retail_stores
WHERE GEO_WITHIN_RADIUS(51.5074, -0.1278, store_lat, store_lng, 50)
ORDER BY distance_from_london_km
Customers grouped by proximity band¶
SELECT
CASE
WHEN GEO_WITHIN_RADIUS(48.8566, 2.3522, customer_lat, customer_lng, 50)
THEN 'Local (< 50km)'
WHEN GEO_WITHIN_RADIUS(48.8566, 2.3522, customer_lat, customer_lng, 300)
THEN 'Regional (50–300km)'
ELSE 'Remote (> 300km)'
END AS proximity_band,
COUNT(*) AS customer_count,
total_revenue
FROM customers
GROUP BY proximity_band
ORDER BY customer_count DESC
Putting it all together¶
Full customer health report¶
This query combines a join, a predefined filter, a window function and measures:
WITH ranked_customers AS (
SELECT
customers.name,
customers.region,
customers.account_manager_id,
total_revenue,
RANK() OVER (PARTITION BY customers.region ORDER BY total_revenue DESC) AS regional_rank
FROM customers[active]
GROUP BY customers.name, customers.region, customers.account_manager_id
)
SELECT
rc.name AS customer,
rc.region,
users.full_name AS account_manager,
rc.total_revenue,
rc.regional_rank
FROM ranked_customers rc
JOIN users ON rc.account_manager_id = users.user_id
WHERE rc.regional_rank <= 3
ORDER BY rc.region, rc.regional_rank
This returns the top 3 customers by revenue in each region, with their account manager.