Hey @TonyC , thank you for getting back to me. Sure thing! Here's the original query I was working with originally for this post:
WITH user_count AS (
SELECT
account_id,
COUNT(account_id) AS count
FROM users
GROUP BY account_id
), admin_count AS (
SELECT
account_id,
COUNT(role) AS count
FROM users
WHERE role = 0
GROUP BY account_id
), editor_count AS (
SELECT
account_id,
COUNT(role) AS count
FROM users
WHERE role = 1
GROUP BY account_id
), viewer_count AS (
SELECT
account_id,
COUNT(role) AS count
FROM users
WHERE role = 2
GROUP BY account_id
), manual_paid_seats AS (
SELECT
ms.account_id,
ms.state,
ms.period_end,
CONCAT_WS('/', COUNT(u.role), ms.paid_seat_count) AS "Paid Seat Usage"
FROM manual_subscriptions ms
LEFT JOIN users u
ON u.account_id = ms.account_id
WHERE ms.state = 'active' AND u.role IN (0, 1)
GROUP BY ms.account_id, ms.state, ms.paid_seat_count, ms.period_end
), stripe_paid_seats AS (
SELECT *
FROM (
SELECT
ps.account_id,
ps.state,
ps.current_period_end,
CONCAT_WS('/', COUNT(u.role), sa.quantity) AS "Paid Seat Usage",
ROW_NUMBER() OVER (
PARTITION BY ps.id ORDER BY sa.starts_at DESC
) AS row_number
FROM subscription_agreements sa
LEFT JOIN payola_subscriptions ps
ON ps.id = sa.payola_subscription_id
LEFT JOIN users u
ON u.account_id = ps.account_id
WHERE ps.state = 'active' AND u.role IN (0, 1)
GROUP BY ps.account_id, ps.state, sa.quantity, ps.current_period_end, sa.starts_at, ps.id) temp where row_number=1
), free_seat_count AS (
SELECT
a.id,
CONCAT_WS('/', COUNT(role), '3') AS "Paid Seat Usage"
FROM accounts a
LEFT JOIN users u
ON u.account_id = a.id
WHERE role IN (0,1) AND a.plan_unique_id = 'free'
GROUP BY a.id
), legacy_plans_seat_count AS (
SELECT
a.id,
CONCAT_WS('/', COUNT(role), '∞') AS "Paid Seat Usage"
FROM accounts a
LEFT JOIN users u
ON u.account_id = a.id
WHERE role IN (0,1) AND a.plan_unique_id IN ('pro_legacy_annual')
GROUP BY a.id
), credit_usage AS (
SELECT
account_id,
ABS(SUM(karma_points)) AS "Credit Usage"
FROM point_transactions
WHERE
cause IN (18, 19)
[[AND {{date_range}}]]
GROUP BY account_id
)
SELECT a.id AS "Account ID",
COALESCE(clearbit_data.company_name, a.organization_name) AS "Company Name",
a.contact_name AS "Main Contact",
CASE
WHEN u.account_owner = true THEN u.name
END AS "Account Owner",
u.email AS "Account Owner Email",
CASE
WHEN a.manual_subscription = true THEN 'Manual Billing'
ELSE 'Self Served'
END AS "Billing Cadence",
CASE a.plan_unique_id
WHEN 'agency' THEN 'Agency (Monthly)'
WHEN 'agency_annual' THEN 'Agency (Annual)'
WHEN 'annual' THEN 'Annual'
WHEN 'basic' THEN 'Basic (Monthly)'
WHEN 'basic_annual' THEN 'Basic (Annual)'
WHEN 'basic_v3' THEN 'Basic (V3, Monthly)'
WHEN 'basic_v3_annual' THEN 'Basic (V3, Annual)'
WHEN 'enterprise' THEN 'Enterprise'
WHEN 'free' THEN 'Free'
WHEN 'pro_legacy' THEN 'Pro Legacy (Monthly)'
WHEN 'pro_legacy_annual' THEN 'Pro Legacy (Annual)'
WHEN 'pro_v2' THEN 'Pro (V2, Monthly)'
WHEN 'pro_v2_annual' THEN 'Pro (V2, Annual)'
WHEN 'pro_v3' THEN 'Pro (V3, Monthly)'
WHEN 'pro_v3_annual' THEN 'Pro (V3, Annual)'
WHEN 'solo' THEN 'Solo (Monthly)'
WHEN 'solo_annual' THEN 'Solo (Annual)'
WHEN 'studio' THEN 'Studio (Monthly)'
WHEN 'studio_annual' THEN 'Studio (Annual)'
WHEN 'team' THEN 'Team (Monthly)'
WHEN 'team_annual' THEN 'Team (Annual)'
WHEN 'team_v2' THEN 'Team (V2, Monthly)'
WHEN 'team_v2_annual' THEN 'Team (V2, Annual)'
END AS "Plan Type",
CASE
WHEN a.manual_subscription = true THEN mps.period_end
WHEN a.manual_subscription = false THEN sps.current_period_end
END AS "Renewal Date",
uc.count AS "Total Number of Users",
CASE
WHEN ac.count IS NULL THEN 0
ELSE ac.count
END AS "Number of Admins",
CASE
WHEN ec.count IS NULL THEN 0
ELSE ec.count
END AS "Number of Editors",
CASE
WHEN vc.count IS NULL THEN 0
ELSE vc.count
END AS "Number of Viewers",
CASE
WHEN a.manual_subscription = true THEN mps."Paid Seat Usage"
WHEN a.manual_subscription = false AND a.plan_unique_id NOT IN ('free', 'pro_legacy_annual') THEN sps."Paid Seat Usage"
WHEN a.manual_subscription = false AND a.plan_unique_id IN ('pro_legacy_annual') THEN lpsc."Paid Seat Usage"
ELSE fsc."Paid Seat Usage"
END AS "Paid Seat Count",
a.karma_points AS "Remaining Credit Balance",
cu."Credit Usage" AS "Credits Used",
alvv.amount/100 AS "Total LTV (USD)"
FROM accounts a
LEFT JOIN users u
ON u.account_id = a.id
LEFT JOIN user_count uc
ON uc.account_id = a.id
LEFT JOIN admin_count ac
ON ac.account_id = a.id
LEFT JOIN editor_count ec
ON ec.account_id = a.id
LEFT JOIN viewer_count vc
ON vc.account_id = a.id
LEFT JOIN manual_paid_seats mps
ON mps.account_id = a.id
LEFT JOIN stripe_paid_seats sps
ON sps.account_id = a.id
LEFT JOIN free_seat_count fsc
ON fsc.id = a.id
LEFT JOIN legacy_plans_seat_count lpsc
ON lpsc.id = a.id
LEFT JOIN account_lifetime_value_views alvv
ON alvv.account_id = a.id
LEFT JOIN credit_usage cu
ON cu.account_id = a.id
LEFT JOIN (
SELECT
account_id,
data->'person'->'employment'->>'role' as person_role,
data->'company'->>'name' as company_name,
data->'company'->'category'->>'sector' as company_sector
FROM clearbit_lookups
GROUP BY
account_id,
person_role,
company_name,
company_sector
) AS clearbit_data ON (clearbit_data.account_id = a.id)
WHERE u.account_owner IS NOT false
AND a.id IN ([[{{account_id}} #]](SELECT id FROM accounts WHERE id IN (146228, 577087, 365462, 319722, 277400, 453612, 862474, 16853, 193684, 505523, 328911, 343558, 548767, 631554, 375521, 245385, 311824, 185398, 118614, 707419, 382942, 23480, 27506, 412194, 455297, 577087, 433524, 454597)))
--(SELECT id FROM accounts WHERE id IN (146228, 577087, 365462, 319722, 277400, 453612, 862474, 16853, 193684, 505523, 328911, 343558, 548767, 631554, 375521, 245385, 311824, 185398, 118614, 707419, 382942, 23480, 27506, 412194, 455297, 577087, 433524, 454597))
GROUP BY
a.id,
clearbit_data.company_name,
u.account_owner,
u.name,
u.email,
uc.count,
ac.count,
ec.count,
vc.count,
mps."Paid Seat Usage",
sps."Paid Seat Usage",
fsc."Paid Seat Usage",
lpsc."Paid Seat Usage",
mps.period_end,
sps.current_period_end,
alvv.amount,
cu."Credit Usage"
ORDER BY alvv.amount DESC
I was able to get it to work by replacing Complex Default Values on the query to a "Field Filter" that links directly to the account Id's and then setting up the default value for the ID's that I want as such:
WITH user_count AS (
SELECT
account_id,
COUNT(account_id) AS count
FROM users
GROUP BY account_id
), admin_count AS (
SELECT
account_id,
COUNT(role) AS count
FROM users
WHERE role = 0
GROUP BY account_id
), editor_count AS (
SELECT
account_id,
COUNT(role) AS count
FROM users
WHERE role = 1
GROUP BY account_id
), viewer_count AS (
SELECT
account_id,
COUNT(role) AS count
FROM users
WHERE role = 2
GROUP BY account_id
), manual_paid_seats AS (
SELECT
ms.account_id,
ms.state,
ms.period_end,
CONCAT_WS('/', COUNT(u.role), ms.paid_seat_count) AS "Paid Seat Usage"
FROM manual_subscriptions ms
LEFT JOIN users u
ON u.account_id = ms.account_id
WHERE ms.state = 'active' AND u.role IN (0, 1)
GROUP BY ms.account_id, ms.state, ms.paid_seat_count, ms.period_end
), stripe_paid_seats AS (
SELECT *
FROM (
SELECT
ps.account_id,
ps.state,
ps.current_period_end,
CONCAT_WS('/', COUNT(u.role), sa.quantity) AS "Paid Seat Usage",
ROW_NUMBER() OVER (
PARTITION BY ps.id ORDER BY sa.starts_at DESC
) AS row_number
FROM subscription_agreements sa
LEFT JOIN payola_subscriptions ps
ON ps.id = sa.payola_subscription_id
LEFT JOIN users u
ON u.account_id = ps.account_id
WHERE ps.state = 'active' AND u.role IN (0, 1)
GROUP BY ps.account_id, ps.state, sa.quantity, ps.current_period_end, sa.starts_at, ps.id) temp where row_number=1
), free_seat_count AS (
SELECT
a.id,
CONCAT_WS('/', COUNT(role), '3') AS "Paid Seat Usage"
FROM accounts a
LEFT JOIN users u
ON u.account_id = a.id
WHERE role IN (0,1) AND a.plan_unique_id = 'free'
GROUP BY a.id
), legacy_plans_seat_count AS (
SELECT
a.id,
CONCAT_WS('/', COUNT(role), '∞') AS "Paid Seat Usage"
FROM accounts a
LEFT JOIN users u
ON u.account_id = a.id
WHERE role IN (0,1) AND a.plan_unique_id IN ('pro_legacy_annual')
GROUP BY a.id
), credit_usage AS (
SELECT
account_id,
ABS(SUM(karma_points)) AS "Credit Usage"
FROM point_transactions
WHERE
cause IN (18, 19)
[[AND {{date_range}}]]
GROUP BY account_id
)
SELECT accounts.id AS "Account ID",
COALESCE(clearbit_data.company_name, accounts.organization_name) AS "Company Name",
accounts.contact_name AS "Main Contact",
CASE
WHEN u.account_owner = true THEN u.name
END AS "Account Owner",
u.email AS "Account Owner Email",
CASE
WHEN accounts.manual_subscription = true THEN 'Manual Billing'
ELSE 'Self Served'
END AS "Billing Cadence",
CASE accounts.plan_unique_id
WHEN 'agency' THEN 'Agency (Monthly)'
WHEN 'agency_annual' THEN 'Agency (Annual)'
WHEN 'annual' THEN 'Annual'
WHEN 'basic' THEN 'Basic (Monthly)'
WHEN 'basic_annual' THEN 'Basic (Annual)'
WHEN 'basic_v3' THEN 'Basic (V3, Monthly)'
WHEN 'basic_v3_annual' THEN 'Basic (V3, Annual)'
WHEN 'enterprise' THEN 'Enterprise'
WHEN 'free' THEN 'Free'
WHEN 'pro_legacy' THEN 'Pro Legacy (Monthly)'
WHEN 'pro_legacy_annual' THEN 'Pro Legacy (Annual)'
WHEN 'pro_v2' THEN 'Pro (V2, Monthly)'
WHEN 'pro_v2_annual' THEN 'Pro (V2, Annual)'
WHEN 'pro_v3' THEN 'Pro (V3, Monthly)'
WHEN 'pro_v3_annual' THEN 'Pro (V3, Annual)'
WHEN 'solo' THEN 'Solo (Monthly)'
WHEN 'solo_annual' THEN 'Solo (Annual)'
WHEN 'studio' THEN 'Studio (Monthly)'
WHEN 'studio_annual' THEN 'Studio (Annual)'
WHEN 'team' THEN 'Team (Monthly)'
WHEN 'team_annual' THEN 'Team (Annual)'
WHEN 'team_v2' THEN 'Team (V2, Monthly)'
WHEN 'team_v2_annual' THEN 'Team (V2, Annual)'
END AS "Plan Type",
CASE
WHEN accounts.manual_subscription = true THEN mps.period_end
WHEN accounts.manual_subscription = false THEN sps.current_period_end
END AS "Renewal Date",
uc.count AS "Total Number of Users",
CASE
WHEN ac.count IS NULL THEN 0
ELSE ac.count
END AS "Number of Admins",
CASE
WHEN ec.count IS NULL THEN 0
ELSE ec.count
END AS "Number of Editors",
CASE
WHEN vc.count IS NULL THEN 0
ELSE vc.count
END AS "Number of Viewers",
CASE
WHEN accounts.manual_subscription = true THEN mps."Paid Seat Usage"
WHEN accounts.manual_subscription = false AND accounts.plan_unique_id NOT IN ('free', 'pro_legacy_annual') THEN sps."Paid Seat Usage"
WHEN accounts.manual_subscription = false AND accounts.plan_unique_id IN ('pro_legacy_annual') THEN lpsc."Paid Seat Usage"
ELSE fsc."Paid Seat Usage"
END AS "Paid Seat Count",
accounts.karma_points AS "Remaining Credit Balance",
cu."Credit Usage" AS "Credits Used",
alvv.amount/100 AS "Total LTV (USD)"
FROM accounts --a
LEFT JOIN users u
ON u.account_id = accounts.id
LEFT JOIN user_count uc
ON uc.account_id = accounts.id
LEFT JOIN admin_count ac
ON ac.account_id = accounts.id
LEFT JOIN editor_count ec
ON ec.account_id = accounts.id
LEFT JOIN viewer_count vc
ON vc.account_id = accounts.id
LEFT JOIN manual_paid_seats mps
ON mps.account_id = accounts.id
LEFT JOIN stripe_paid_seats sps
ON sps.account_id = accounts.id
LEFT JOIN free_seat_count fsc
ON fsc.id = accounts.id
LEFT JOIN legacy_plans_seat_count lpsc
ON lpsc.id = accounts.id
LEFT JOIN account_lifetime_value_views alvv
ON alvv.account_id = accounts.id
LEFT JOIN credit_usage cu
ON cu.account_id = accounts.id
LEFT JOIN (
SELECT
account_id,
data->'person'->'employment'->>'role' as person_role,
data->'company'->>'name' as company_name,
data->'company'->'category'->>'sector' as company_sector
FROM clearbit_lookups
GROUP BY
account_id,
person_role,
company_name,
company_sector
) AS clearbit_data ON (clearbit_data.account_id = accounts.id)
WHERE u.account_owner IS NOT false
[[AND {{account_id}}]]
[[AND {{plan_type}}]]
[[AND {{manual_billing}}]]
GROUP BY
accounts.id,
clearbit_data.company_name,
u.account_owner,
u.name,
u.email,
uc.count,
ac.count,
ec.count,
vc.count,
mps."Paid Seat Usage",
sps."Paid Seat Usage",
fsc."Paid Seat Usage",
lpsc."Paid Seat Usage",
mps.period_end,
sps.current_period_end,
alvv.amount,
cu."Credit Usage"
ORDER BY alvv.amount DESC
However, this limits me to only be able to update the filters for those 27 selections only. It would be nice to have something where perhaps I could add a "tag option" for "Key Accounts" in the field filter's dropdown menu that has the ID values I want stored in it [for example {{key_accounts}} = account_id IN (id1, id2, ..., idn)
], so that when you select it and run the query it shows the results for those particular selections, but if you remove that option from the filter, it returns a full database query instead, but it looks like it's a feature request at this stage based on other threads I've seen.
Let me know if you have any suggestions, cheers!