Partial Filters

SELECT COUNT(*) FROM $$ORDERS

WITH current_period AS (
  SELECT COUNT(*) AS orders_in_period
  FROM ORDERS              -- filtered: reflects the selected period
),
all_time AS (
  SELECT COUNT(*) AS total_orders
  FROM $$ORDERS            -- not filtered: always all-time
)
SELECT
  ROUND(
    (SELECT orders_in_period FROM current_period) * 100.0 /
    NULLIF((SELECT total_orders FROM all_time), 0),
    2
  ) AS percentage_of_total;

SELECT
  (SELECT SUM(amount) FROM SALES)   AS filtered_revenue,   -- moves with filters
  (SELECT AVG(amount) FROM $$SALES) AS company_average;    -- stays constant

SELECT
  u.region,
  COUNT(o.id)      AS filtered_orders,
  ref.total_users
FROM ORDERS o                       -- filtered
JOIN USERS u ON u.id = o.user_id    -- filtered
LEFT JOIN (
  SELECT region, COUNT(*) AS total_users
  FROM $$USERS                      -- not filtered: full population per region
  GROUP BY region
) ref ON ref.region = u.region
GROUP BY u.region, ref.total_users;