Ability to Use Field Filters from Base SQL Queries in Pivot Tables

Feature Requests
1

Hi everyone,

I have a SQL query that prepares data for use in a pivot table. In the base query, I’m using a field filter (e.g., {{date_filter}}) to filter by the creation date.

I would like to request a feature that allows these field filters defined in the base SQL query to be available and usable in the filters section when creating a pivot table. Currently, I can only filter by the columns returned by the base query, but not by the field filters defined within it.

This would make pivot tables much more flexible and allow for more dynamic filtering options based on the logic already defined in the base query.

Here’s an example of the SQL I’m using:

WITH
    _1 AS (
        SELECT
            EXTRACT(HOUR FROM created_at)::int AS hour,
            EXTRACT(HOUR FROM created_at)::int / 3 * 3 AS hour_group,
            uuid
        FROM orders
        WHERE {{date_filter}} AND status != 'Отменен'
    ),
    _2 AS (SELECT generate_series(0, 23, 3) AS hour),
    _2_1 AS (
        SELECT hour AS from_hour,
               lag(hour, 1, 24) OVER (ORDER BY hour DESC) AS to_hour
        FROM _2
    ),
    _3 AS (
        SELECT
            _2.hour AS hour_group,
            oi.product_id,
            COUNT(product_id) AS quantity
        FROM _1
        RIGHT JOIN _2 ON hour_group = _2.hour
        LEFT JOIN order_items oi ON oi.order_id = _1.uuid
        GROUP BY _2.hour, product_id
        ORDER BY _2.hour
    ),
    _4 AS (
        SELECT
            from_hour,
            to_hour,
            product_id,
            quantity
        FROM _3
        LEFT JOIN _2_1 ON _2_1.from_hour = _3.hour_group
        ORDER BY hour_group
    ),
    _5 AS (
        SELECT
            TO_CHAR((INTERVAL '1 hour' * from_hour)::time, 'HH24:MI') AS from_hour,
            TO_CHAR((INTERVAL '1 hour' * to_hour)::time, 'HH24:MI') AS to_hour,
            from_hour AS hour_group,
            product_id,
            quantity
        FROM _4
    ),
    _6 AS (
        SELECT *,
            ROW_NUMBER() OVER (PARTITION BY hour_group ORDER BY quantity DESC) AS row_num
        FROM _5
    )

SELECT
    CONCAT(from_hour, '-', to_hour) AS time,
    p.name,
    quantity,
    row_num
FROM _6
INNER JOIN products p ON p.uuid = product_id
ORDER BY time, quantity DESC;

2025-04-15_16-40-40
2025-04-15_16-40-403666×1484 220 KB

2

I'm assuming you don't want to leverage dashboard filters.

I'm curious: why don't you want to expose the column(s) in your final select (just for filtering purposes)?
you could do something like this:

SELECT
  CONCAT(from_hour, '-', to_hour) AS time,
  p.name,
  quantity,
  row_num,
  created_at::date AS order_date    -- expose this column
FROM _6

and then use the column in the UI to filter.

I recently had to prep some user funnel data for which I wished to preserve filtering fields and I approached it like this:

WITH raw_data AS (
  SELECT *
  FROM {{schema_name}}.{{table_name}}
  WHERE 1 = 1
    [[AND {{filter_date}}]]       -- e.g. date field filter
    [[AND {{filter_status}}]]     -- e.g. status field filter
    [[AND {{filter_custom}}]]     -- any other Metabase filter
)

SELECT
  'Stage 1: …'        AS "Funnel Stage",
  COUNT(*)            AS "Count"
FROM raw_data
WHERE {{condition_stage1}}

UNION ALL

SELECT
  'Stage 2: …'        AS "Funnel Stage",
  COUNT(*)            AS "Count"
FROM raw_data
WHERE {{condition_stage2}}

UNION ALL

SELECT
  'Stage 3: …'        AS "Funnel Stage",
  COUNT(*)            AS "Count"
FROM raw_data
WHERE {{condition_stage3}}

Then I set variables connected to the field filters in my dashboard. Have you tried something like this?

3

My query returns aggregated data. It is grouped by hours and products. So I can't put the date in the result columns.