Analytic SQL Examples¶

Real-world examples of Analytic SQL queries. Each example shows the Analytic SQL syntax alongside the generated SQL for different database dialects.

Try these queries yourself: play.analyticsql.com

Table of Contents¶

Basic Queries
Filtering
Aggregations
Sorting and Limiting
Complex Queries

Basic Queries¶

Simple FROM¶

ASQL

ASQL Playground

from users

PostgreSQLMySQLBigQuerySnowflake

SELECT * FROM users

SELECT * FROM users

SELECT * FROM users

SELECT * FROM users

FROM with WHERE¶

ASQL

ASQL Playground

from users
    where status = "active"

PostgreSQLMySQLBigQuerySnowflake

SELECT * FROM users WHERE status = 'active'

SELECT * FROM users WHERE status = 'active'

SELECT * FROM users WHERE status = 'active'

SELECT * FROM users WHERE status = 'active'

FROM with SELECT¶

ASQL

ASQL Playground

from users
    select name, email

PostgreSQLMySQLBigQuerySnowflake

SELECT name, email FROM users

SELECT name, email FROM users

SELECT name, email FROM users

SELECT name, email FROM users

FROM WHERE SELECT¶

ASQL

ASQL Playground

from users
    where status = "active"
    select name, email

PostgreSQLMySQLBigQuerySnowflake

SELECT name, email FROM users WHERE status = 'active'

SELECT name, email FROM users WHERE status = 'active'

SELECT name, email FROM users WHERE status = 'active'

SELECT name, email FROM users WHERE status = 'active'

Filtering¶

Comparison Operators¶

ASQL:

ASQL Playground

from users
where age < 18

SQL (PostgreSQL):

SELECT * FROM users WHERE age < 18

ASQL:

ASQL Playground

from users
where age > 65

SQL (PostgreSQL):

SELECT * FROM users WHERE age > 65

ASQL:

ASQL Playground

from users
where age >= 18

SQL (PostgreSQL):

SELECT * FROM users WHERE age >= 18

ASQL:

ASQL Playground

from users
where status != "inactive"

SQL (PostgreSQL):

SELECT * FROM users WHERE status <> 'inactive'

NULL Checks¶

ASQL:

ASQL Playground

from users
where email is null

SQL (PostgreSQL):

SELECT * FROM users WHERE email IS NULL

ASQL:

ASQL Playground

from users
where email is not null

SQL (PostgreSQL):

SELECT * FROM users WHERE email IS NOT NULL

Logical Operators¶

ASQL:

ASQL Playground

from users
where status = "active"
    and age >= 18

SQL (PostgreSQL):

SELECT * FROM users WHERE status = 'active' AND age >= 18

ASQL:

ASQL Playground

from users
where status = "active"
    or status = "pending"

SQL (PostgreSQL):

SELECT * FROM users WHERE status = 'active' OR status = 'pending'

ASQL:

ASQL Playground

from users
where not status = "inactive"

SQL (PostgreSQL):

SELECT * FROM users WHERE NOT status = 'inactive'

Multiple Conditions¶

ASQL:

ASQL Playground

from users
where status = "active"
    and age >= 18
    and email is not null

SQL (PostgreSQL):

SELECT * FROM users WHERE status = 'active' AND age >= 18 AND email IS NOT NULL

IN Operator¶

ASQL:

ASQL Playground

from users
where status in ("active", "pending", "verified")

SQL (PostgreSQL):

SELECT * FROM users WHERE status IN ('active', 'pending', 'verified')

ASQL:

ASQL Playground

from users
where age in (18, 19, 20, 21)

SQL (PostgreSQL):

SELECT * FROM users WHERE age IN (18, 19, 20, 21)

NOT IN Operator¶

ASQL:

ASQL Playground

from users
where status not in ("inactive", "deleted", "banned")

SQL (PostgreSQL):

SELECT * FROM users WHERE NOT status IN ('inactive', 'deleted', 'banned')

Aggregations¶

GROUP BY with COUNT (#)¶

ASQL:

ASQL Playground

from users
group by country (# as total_users)

SQL (PostgreSQL):

SELECT country, COUNT(*) AS total_users FROM users GROUP BY country

GROUP BY with SUM¶

ASQL:

ASQL Playground

from sales
group by region (sum(amount) as revenue)

SQL (PostgreSQL):

SELECT region, SUM(amount) AS revenue FROM sales GROUP BY region

GROUP BY with AVG¶

ASQL:

ASQL Playground

from users
group by country (avg(age) as avg_age)

SQL (PostgreSQL):

SELECT country, AVG(age) AS avg_age FROM users GROUP BY country

Multiple Aggregations¶

ASQL:

ASQL Playground

from sales
group by region (
    sum(amount) as revenue,
    # as orders,
    avg(amount) as avg_order
)

SQL (PostgreSQL):

SELECT region, SUM(amount) AS revenue, COUNT(*) AS orders, AVG(amount) AS avg_order
FROM sales
GROUP BY region

Multiple Grouping Columns¶

ASQL:

ASQL Playground

from sales
group by region, month (sum(amount) as revenue)

SQL (PostgreSQL):

SELECT region, month, SUM(amount) AS revenue FROM sales GROUP BY region, month

GROUP BY with WHERE¶

ASQL:

ASQL Playground

from sales
where year = 2024
group by region (sum(amount) as revenue)

SQL (PostgreSQL):

SELECT region, SUM(amount) AS revenue
FROM sales
WHERE year = 2024
GROUP BY region

All Aggregation Functions¶

ASQL:

ASQL Playground

from sales
group by region (
    sum(amount) as total_revenue,
    avg(amount) as avg_order,
    # as order_count,
    min(amount) as min_order,
    max(amount) as max_order
)

SQL (PostgreSQL):

SELECT
    region,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order,
    COUNT(*) AS order_count,
    MIN(amount) AS min_order,
    MAX(amount) AS max_order
FROM sales
GROUP BY region

Sorting and Limiting¶

SORT Ascending¶

ASQL:

ASQL Playground

from users
order by name

SQL (PostgreSQL):

SELECT * FROM users ORDER BY name ASC

SORT Descending¶

ASQL:

ASQL Playground

from users
order by -total_users

SQL (PostgreSQL):

SELECT * FROM users ORDER BY total_users DESC

Multiple Sort Columns¶

ASQL:

ASQL Playground

from users
order by -total_users, name

SQL (PostgreSQL):

SELECT * FROM users ORDER BY total_users DESC, name ASC

TAKE/LIMIT¶

ASQL:

ASQL Playground

from users
limit 10

SQL (PostgreSQL):

SELECT * FROM users LIMIT 10

GROUP BY + SORT¶

ASQL:

ASQL Playground

from users
group by country (# as total_users)
order by -total_users

SQL (PostgreSQL):

SELECT country, COUNT(*) AS total_users
FROM users
GROUP BY country
ORDER BY total_users DESC

Complete Pipeline¶

ASQL:

ASQL Playground

from users
where status = "active"
group by country (# as total_users)
order by -total_users
limit 10

SQL (PostgreSQL):

SELECT country, COUNT(*) AS total_users
FROM users
WHERE status = 'active'
GROUP BY country
ORDER BY total_users DESC
LIMIT 10

Sort by Column (Descending)¶

ASQL:

ASQL Playground

from users
order by -updated_at

SQL (PostgreSQL):

SELECT * FROM users ORDER BY updated_at DESC

ASQL:

ASQL Playground

from users
order by -updated_at, name

SQL (PostgreSQL):

SELECT * FROM users ORDER BY updated_at DESC, name ASC

The - prefix makes it easy to order by by columns in descending order. It also works with function calls like order by -month(created_at).

Complex Queries¶

Complex Analytics Query¶

ASQL:

ASQL Playground

from sales
where status = "completed" and amount > 100
group by region, month (
    sum(amount) as revenue,
    # as order_count,
    avg(amount) as avg_order
)
order by -revenue
limit 20

SQL (PostgreSQL):

SELECT
    region,
    month,
    SUM(amount) AS revenue,
    COUNT(*) AS order_count,
    AVG(amount) AS avg_order
FROM sales
WHERE status = 'completed' AND amount > 100
GROUP BY region, month
ORDER BY revenue DESC
LIMIT 20

User Analytics¶

ASQL:

ASQL Playground

from users
where status = "active" and age >= 18 and email is not null
group by country (
    # as total_users,
    avg(age) as avg_age
)
order by -total_users

SQL (PostgreSQL):

SELECT
    country,
    COUNT(*) AS total_users,
    AVG(age) AS avg_age
FROM users
WHERE status = 'active' AND age >= 18 AND email IS NOT NULL
GROUP BY country
ORDER BY total_users DESC

Sales Report¶

ASQL:

ASQL Playground

from sales
where (status = "completed" or status = "pending")
    and amount >= 50
    and created_at is not null
group by product_category (
    sum(amount) as total_revenue,
    # as total_orders,
    avg(amount) as avg_order_value,
    max(amount) as max_order_value
)
order by -total_revenue
limit 10

SQL (PostgreSQL):

SELECT
    product_category,
    SUM(amount) AS total_revenue,
    COUNT(*) AS total_orders,
    AVG(amount) AS avg_order_value,
    MAX(amount) AS max_order_value
FROM sales
WHERE (status = 'completed' OR status = 'pending')
    AND amount >= 50
    AND created_at IS NOT NULL
GROUP BY product_category
ORDER BY total_revenue DESC
LIMIT 10

Cohort Analysis¶

Cohort analysis tracks user behavior over time by grouping users by when they started. In ASQL, this is straightforward using computed columns and alias reuse.

User Retention by Cohort¶

ASQL:

ASQL Playground

from events
join users
group by
  month(users.signup_date) as cohort,
  months_between(events.event_date, users.signup_date) as period
(
  count(distinct user_id) as active
)
order by cohort, period

ASQL's alias reuse lets you reference cohort immediately after defining it - no CTEs needed!

Revenue Cohort with LTV¶

ASQL:

ASQL Playground

from orders
join customers
group by
  month(customers.first_order_date) as cohort,
  months_between(orders.order_date, customers.first_order_date) as period
(
  sum(total) as revenue,
  count(distinct customer_id) as customers
)
select *,
  first(customers) over cohort as cohort_size,
  running_sum(revenue) over cohort / cohort_size as ltv
order by cohort, period

Retention with Percentages¶

ASQL:

ASQL Playground

from events
join users
group by
  month(users.signup_date) as cohort,
  months_between(events.event_date, users.signup_date) as period
(
  count(distinct user_id) as active
)
select *,
  first(active) over cohort as cohort_size,
  round(active::numeric / cohort_size * 100, 1) as retention_pct
order by cohort, period

Segmented Cohorts by Channel¶

ASQL:

ASQL Playground

from events
join users
group by
  users.channel,
  month(users.signup_date) as cohort,
  months_between(events.event_date, users.signup_date) as period
(
  count(distinct user_id) as active
)
order by channel, cohort, period

This creates cohorts segmented by acquisition channel, allowing you to compare retention across different channels.

Try It Yourself¶

The easiest way to try these examples is in the Interactive Playground. Just copy any ASQL query from the examples above and paste it into the playground to see the generated SQL in real-time.

You can also use ASQL as a Python library:

import asql

asql_query = """
from users
where status = "active"
group by country ( # as total_users )
order by -total_users
limit 10
"""

sql = asql.transpile(asql_query, write="postgres")[0]
print(sql)