AQLearn 1.7 / Practice

Practice (1)

Source lesson: AQLearn 1.7: Practice (1). Tangible win: combine aggregate, dimension, filter, and relationship in five small problems and answer the chapter-1 capstone question.

Purpose of this lesson

Have you ever tried to answer "How many distinct products did our 2022 sign-ups in London end up ordering"?

You hit these pain points:

The answer involves four models (order_items, orders, users, cities). Forgetting one join silently changes the number.
You mix up count with count_distinct. Customers see the "wrong" total but you cannot point at the mistake.
You re-write the query from scratch every time the filter shifts (2022 in London, 2023 in Sydney, last year in Singapore). It is slow and error-prone.

This lesson solves it: walk five layered tasks that combine everything from chapter 1, and finish with the capstone question above. Each task adds one element; the final query has a measure, two filters, and a multi-hop relationship.

Why this matters for your mission: any presales scoping conversation reduces to a chain like this. If you can deliver the chain end-to-end in AQL, you can stop reaching for SQL on calls.

Dataset diagram

This practice spans the full chain: order_items > orders > users > cities > countries and order_items > products. Every model in the diagram below is in play.

The capstone touches every relationship in the dataset. Highlighted fields are used directly: users.email, users.sign_up_at, cities.name, countries.name, orders.id, order_items.product_id, products.id.

primary key foreign key

Tasks (build the query one step at a time)

Try each task in AQLearn before opening the solution. Solutions use simplified auto-generated SQL after each step.

Count of orders per user email

Measure count_orders for each user_email.

Solution

explore {
  measures {
    count_orders: orders | count(orders.id),
  }
  dimensions {
    user_email: users.email,
  }
}

Join path: orders > users via orders.user_id.

select
  users.email as user_email
  , count(orders.id) as count_orders
from orders
left join users on orders.user_id = users.id
group by users.email

Add distinct product count

Add a measure count_distinct_products. Hint: count_distinct() (see aggregator functions).

Solution

explore {
  measures {
    count_orders: orders | count(orders.id),
    count_distinct_products: order_items | count_distinct(order_items.product_id),
  }
  dimensions {
    user_email: users.email,
  }
}

Distinct counts dedupe values inside each group. AQL adds order_items to the join chain because the new measure references it.

select
  users.email as user_email
  , count(orders.id) as count_orders
  , count(distinct order_items.product_id) as count_distinct_products
from orders
left join users on orders.user_id = users.id
left join order_items on order_items.order_id = orders.id
group by users.email

Break down by user sign-up year

Replace the email dimension with the year of users.sign_up_at.

Solution

explore {
  measures {
    count_orders: orders | count(orders.id),
    count_distinct_products: order_items | count_distinct(order_items.product_id),
  }
  dimensions {
    sign_up_year: users.sign_up_at | year(),
  }
}

select
  date_trunc('year', users.sign_up_at) as sign_up_year
  , count(orders.id) as count_orders
  , count(distinct order_items.product_id) as count_distinct_products
from orders
left join users on orders.user_id = users.id
left join order_items on order_items.order_id = orders.id
group by date_trunc('year', users.sign_up_at)

Add country breakdown

Break down by both sign-up year and country name. The relationship from users to countries via cities is already defined; just reference countries.name.

Solution

explore {
  measures {
    count_orders: orders | count(orders.id),
    count_distinct_products: order_items | count_distinct(order_items.product_id),
  }
  dimensions {
    sign_up_year: users.sign_up_at | year(),
    country_name: countries.name,
  }
}

Two new joins are added automatically: users > cities > countries.

select
  date_trunc('year', users.sign_up_at) as sign_up_year
  , countries.name as country_name
  , count(orders.id) as count_orders
  , count(distinct order_items.product_id) as count_distinct_products
from orders
left join users on orders.user_id = users.id
left join order_items on order_items.order_id = orders.id
left join cities on users.city_id = cities.id
left join countries on cities.country_code = countries.code
group by date_trunc('year', users.sign_up_at), countries.name

Capstone: distinct products from London 2022 sign-ups

"Count of distinct products ordered by all users who signed up in 2022 in London."

Solution

explore {
  measures {
    count_distinct_products: order_items | count_distinct(order_items.product_id),
  }
  filters {
    users.sign_up_at | year() == @2022,
    cities.name == 'London',
  }
}

No dimensions, so the result is a single number. Two filters narrow rows before the distinct count runs.

select
  count(distinct order_items.product_id) as count_distinct_products
from order_items
left join orders on order_items.order_id = orders.id
left join users on orders.user_id = users.id
left join cities on users.city_id = cities.id
where date_trunc('year', users.sign_up_at) = '2022-01-01'
  and cities.name = 'London'

Customer-safe explanation: "We start from the items, walk the relationships back to users and cities, narrow to London sign-ups in 2022, then count the distinct products. AQL writes the joins from the schema we already defined."

Retrieval practice

1. Which aggregate gets a deduped count of order_items.product_id? count(order_items.product_id) count_distinct(order_items.product_id) sum(order_items.product_id)

2. To break down by country, you do what? write the JOIN to cities and countries by hand just reference countries.name in dimensions; the dataset relationships handle joins redefine the dataset

3. The two filters (year == 2022 and cities.name == 'London') run... before the distinct count, at step 5 in the pipeline after the distinct count, at step 7 are ignored because there are no dimensions

4. With no dimensions, the capstone returns... one number one row per user one row per city

Do in AQLearn

Open AQLearn Practice (1).
Solve task 1, then task 2, etc. Run after each step and compare the result to the AQLearn-provided checks.
Open the Generated SQL tab after task 4 to confirm the join chain matches the one shown here.
When you reach the capstone, write the final answer as a single number and say out loud what every line of AQL is doing.

Next local lesson

Next chapter starts with Metric Expressions: how to name and reuse aggregations across many reports.