nikitasavelyev425 opened a new issue, #34894:
URL: https://github.com/apache/superset/issues/34894
### Bug description
Not duplicate of #31365!
1. Create virtual dataset that applies time filter in inner query using
get_time_filter macro
Example query:
```
{% set time_filter = get_time_filter("dttm", remove_filter=True,
target_type='DATE') %}
{% set time_filter2 = get_time_filter("dttm", remove_filter=True,
target_type='DATE') %}
{% set from_expr = time_filter.from_expr %}
{% set to_expr = time_filter.to_expr %}
WITH filtered_trans AS(
SELECT
etm.amount / etm.exchange_rate AS amount_usdt,
etm.amount / 1000000 AS amount_fiat,
etm.final_status_timestamp::DATE "uct"
FROM public.external_transaction_model etm
WHERE etm.status = 'accept'
AND etm.exchange_rate != 0
{% if from_expr %}AND etm.final_status_timestamp::DATE >= {{
from_expr }}{% endif %}
{% if to_expr %}AND etm.final_status_timestamp::DATE < {{ to_expr
}}{% endif %}
{% if get_filters('geo_fltr')|length > 0 %}AND etm.currency_id
IN{{get_filters('geo_fltr', remove_filter=True)[0].get('val')|where_in}}{%
endif %}
{% if get_filters('direction_fltr')|length > 0 %}AND etm.direction =
'{{get_filters('direction_fltr', remove_filter=True)[0].get('val')}}'{% endif %}
),
filtered_trust AS(
SELECT
ubcm.create_timestamp::DATE AS uct,
SUM(ubcm.trust_balance) / 1000000 AS trust_balance
FROM public.external_transaction_model etm
JOIN public.user_balance_change_model ubcm
on etm.id = transaction_id
AND ubcm.user_id = '5f34d784-cf37-4cc2-830b-793d84580f84'
WHERE etm.status = 'accept'
AND etm.exchange_rate != 0
{% if from_expr %}AND ubcm.create_timestamp::DATE >= {{ from_expr
}}{% endif %}
{% if to_expr %}AND ubcm.create_timestamp::DATE < {{ to_expr }}{%
endif %}
{% if get_filters('geo_fltr')|length > 0 %}AND etm.currency_id
IN{{get_filters('geo_fltr', remove_filter=True)[0].get('val')|where_in}}{%
endif %}
{% if get_filters('direction_fltr')|length > 0 %}AND etm.direction =
'{{get_filters('direction_fltr', remove_filter=True)[0].get('val')}}'{% endif %}
GROUP BY ubcm.create_timestamp::DATE
),
aggregated AS(
SELECT
trans.uct
AS day,
SUM(trans.amount_usdt)
AS "общий объем",
SUM(CASE WHEN trans.amount_fiat >= 1000 AND trans.amount_fiat < 2001
THEN trans.amount_usdt END) AS "1000-2000",
SUM(CASE WHEN trans.amount_fiat >= 2001 AND trans.amount_fiat < 3001
THEN trans.amount_usdt END) AS "2001-3000",
SUM(CASE WHEN trans.amount_fiat >= 3001 AND trans.amount_fiat < 4001
THEN trans.amount_usdt END) AS "3001-4000",
SUM(CASE WHEN trans.amount_fiat >= 4001 AND trans.amount_fiat < 5001
THEN trans.amount_usdt END) AS "4001-5000",
SUM(CASE WHEN trans.amount_fiat >= 5001 AND trans.amount_fiat < 6001
THEN trans.amount_usdt END) AS "5001-6000",
SUM(CASE WHEN trans.amount_fiat >= 6001 AND trans.amount_fiat < 7001
THEN trans.amount_usdt END) AS "6001-7000",
SUM(CASE WHEN trans.amount_fiat >= 7001 AND trans.amount_fiat < 8001
THEN trans.amount_usdt END) AS "7001-8000",
SUM(CASE WHEN trans.amount_fiat >= 8001
THEN trans.amount_usdt END) AS "8001-inf",
SUM(CASE WHEN trans.amount_fiat >= 1001 AND trans.amount_fiat <
20001 THEN trans.amount_usdt END) AS "1001-20000",
SUM(CASE WHEN trans.amount_fiat >= 20001
THEN trans.amount_usdt END) AS "20001-inf"
FROM filtered_trans trans
GROUP BY trans.uct
ORDER BY trans.uct
)
SELECT
day,
"общий объем",
"1000-2000",
"2001-3000",
"3001-4000",
"4001-5000",
"5001-6000",
"6001-7000",
"7001-8000",
"8001-inf",
"1001-20000",
"20001-inf",
trust.trust_balance AS "прибыль",
CASE
WHEN "общий объем" = 0
THEN 0
ELSE round(trust.trust_balance / "общий объем" * 100, 2)
END AS "маржинальность",
'' AS direction_fltr
FROM aggregated K
LEFT JOIN filtered_trust trust
ON trust.uct = K.day
```
2. Create simple RAW_RECORDS chart using new dataset
<img width="1515" height="1039" alt="Image"
src="https://github.com/user-attachments/assets/65a13698-1ba1-452d-8add-2406e22423ab";
/>
3. Add new chart to dashboard with time filter and see the resulting
query<br>
Time filter applies twice<br>
```
SELECT day AS day, "общий объем" AS "общий объем", "1000-2000" AS
"1000-2000", "2001-3000" AS "2001-3000", "3001-4000" AS "3001-4000",
"4001-5000" AS "4001-5000", "5001-6000" AS "5001-6000", "6001-7000" AS
"6001-7000", "7001-8000" AS "7001-8000", "8001-inf" AS "8001-inf", "1001-20000"
AS "1001-20000", "20001-inf" AS "20001-inf", "прибыль" AS "прибыль",
"маржинальность" AS "маржинальность"
FROM (
WITH filtered_trans AS(
SELECT
etm.amount / etm.exchange_rate AS amount_usdt,
etm.amount / 1000000 AS amount_fiat,
etm.final_status_timestamp::DATE "uct"
FROM public.external_transaction_model etm
WHERE etm.status = 'accept'
AND etm.exchange_rate != 0
AND etm.final_status_timestamp::DATE >= TO_DATE('2025-07-28',
'YYYY-MM-DD')
AND etm.final_status_timestamp::DATE < TO_DATE('2025-08-28',
'YYYY-MM-DD')
AND etm.currency_id IN('RUB')
AND etm.direction = 'outbound'
),
filtered_trust AS(
SELECT
ubcm.create_timestamp::DATE AS uct,
SUM(ubcm.trust_balance) / 1000000 AS trust_balance
FROM public.external_transaction_model etm
JOIN public.user_balance_change_model ubcm
on etm.id = transaction_id
AND ubcm.user_id = '5f34d784-cf37-4cc2-830b-793d84580f84'
WHERE etm.status = 'accept'
AND etm.exchange_rate != 0
AND ubcm.create_timestamp::DATE >= TO_DATE('2025-07-28',
'YYYY-MM-DD')
AND ubcm.create_timestamp::DATE < TO_DATE('2025-08-28',
'YYYY-MM-DD')
AND etm.currency_id IN('RUB')
AND etm.direction = 'outbound'
GROUP BY ubcm.create_timestamp::DATE
),
aggregated AS(
SELECT
trans.uct
AS day,
SUM(trans.amount_usdt)
AS "общий объем",
SUM(CASE WHEN trans.amount_fiat >= 1000 AND trans.amount_fiat < 2001
THEN trans.amount_usdt END) AS "1000-2000",
SUM(CASE WHEN trans.amount_fiat >= 2001 AND trans.amount_fiat < 3001
THEN trans.amount_usdt END) AS "2001-3000",
SUM(CASE WHEN trans.amount_fiat >= 3001 AND trans.amount_fiat < 4001
THEN trans.amount_usdt END) AS "3001-4000",
SUM(CASE WHEN trans.amount_fiat >= 4001 AND trans.amount_fiat < 5001
THEN trans.amount_usdt END) AS "4001-5000",
SUM(CASE WHEN trans.amount_fiat >= 5001 AND trans.amount_fiat < 6001
THEN trans.amount_usdt END) AS "5001-6000",
SUM(CASE WHEN trans.amount_fiat >= 6001 AND trans.amount_fiat < 7001
THEN trans.amount_usdt END) AS "6001-7000",
SUM(CASE WHEN trans.amount_fiat >= 7001 AND trans.amount_fiat < 8001
THEN trans.amount_usdt END) AS "7001-8000",
SUM(CASE WHEN trans.amount_fiat >= 8001
THEN trans.amount_usdt END) AS "8001-inf",
SUM(CASE WHEN trans.amount_fiat >= 1001 AND trans.amount_fiat <
20001 THEN trans.amount_usdt END) AS "1001-20000",
SUM(CASE WHEN trans.amount_fiat >= 20001
THEN trans.amount_usdt END) AS "20001-inf"
FROM filtered_trans trans
GROUP BY trans.uct
ORDER BY trans.uct
)
SELECT
day,
"общий объем",
"1000-2000",
"2001-3000",
"3001-4000",
"4001-5000",
"5001-6000",
"6001-7000",
"7001-8000",
"8001-inf",
"1001-20000",
"20001-inf",
trust.trust_balance AS "прибыль",
CASE
WHEN "общий объем" = 0
THEN 0
ELSE round(trust.trust_balance / "общий объем" * 100, 2)
END AS "маржинальность",
'' AS direction_fltr
FROM aggregated K
LEFT JOIN filtered_trust trust
ON trust.uct = K.day
) AS virtual_table
WHERE day >= TO_DATE('2025-07-28', 'YYYY-MM-DD') AND day <
TO_DATE('2025-08-28', 'YYYY-MM-DD')
LIMIT 1000;
```
### Screenshots/recordings
_No response_
### Superset version
5.0.0
### Python version
3.10
### Node version
I don't know
### Browser
Firefox
### Additional context
Dataset with following query works fine, filter applies only in inner query
```
{% set time_filter = get_time_filter("dttm", remove_filter=True,
target_type='DATE') %}
{% set from_expr = time_filter.from_expr %}
{% set to_expr = time_filter.to_expr %}
WITH filtered_data AS(
SELECT
etm.id,
etm.create_timestamp,
etm.final_status_timestamp,
etm.amount,
etm.status,
etm.exchange_rate,
EXISTS(
SELECT
FROM public.appeals a
WHERE a.transaction_id = etm.id
) AS appeal
FROM public.external_transaction_model etm
{% if get_filters('merch_fltr')|length > 0 %}
JOIN public.user_model um_m
ON um_m.id = etm.merchant_id
AND um_m.role = 'merchant'
AND um_m.name IN{{get_filters('merch_fltr',
remove_filter=True)[0].get('val')|where_in}}
{% endif %}
{% if get_filters('team_fltr')|length > 0 %}
JOIN public.user_model um_t
ON um_t.id = etm.team_id
AND um_t.role = 'team'
AND um_t.name IN{{get_filters('team_fltr',
remove_filter=True)[0].get('val')|where_in}}
{% endif %}
WHERE TRUE
{% if get_filters('direction_fltr')|length > 0 %}AND etm.direction =
'{{get_filters('direction_fltr', remove_filter=True)[0].get('val')}}'{% endif %}
{% if from_expr %}AND etm.final_status_timestamp::DATE >= {{
from_expr }}{% endif %}
{% if to_expr %}AND etm.final_status_timestamp::DATE < {{ to_expr
}}{% endif %}
{% if get_filters('geo_fltr')|length > 0 %}AND etm.currency_id
IN{{get_filters('geo_fltr', remove_filter=True)[0].get('val')|where_in}}{%
endif %}
{% if get_filters('type_fltr')|length > 0 %}AND etm.type
IN{{get_filters('type_fltr', remove_filter=True)[0].get('val')|where_in}}{%
endif %}
),
aggregated_data AS MATERIALIZED(
SELECT
{% if get_filters('partition_by_hour', remove_filter=True)|length > 0 %}
EXTRACT(HOUR FROM etm.final_status_timestamp)::TEXT AS day_or_hour,
{% else %}
TO_CHAR(etm.final_status_timestamp::DATE, 'dd.mm.yyyy') AS
day_or_hour,
{% endif %}
COUNT(*) AS trans_count,
COUNT(*) FILTER (WHERE status = 'accept') AS success_count,
(SUM(etm.amount) FILTER (WHERE etm.status = 'accept')) / 1000000 AS
sum_success_amount,
(AVG(etm.amount) FILTER (WHERE etm.status = 'accept')) / 1000000 AS
avg_success_amount,
ROUND(EXTRACT(EPOCH FROM AVG(etm.final_status_timestamp -
etm.create_timestamp) FILTER (WHERE etm.status = 'accept')))::INTEGER AS
avg_success_time,
ROUND(EXTRACT(EPOCH FROM AVG(etm.final_status_timestamp -
etm.create_timestamp) FILTER (WHERE etm.status = 'close' )))::INTEGER AS
avg_reject_time,
COUNT(*) FILTER (WHERE appeal) AS appeal_count
FROM filtered_data etm
{% if get_filters('partition_by_hour', remove_filter=True)|length > 0 %}
GROUP BY EXTRACT(HOUR FROM etm.final_status_timestamp)
ORDER BY EXTRACT(HOUR FROM etm.final_status_timestamp) DESC
{% else %}
GROUP BY etm.final_status_timestamp::DATE
ORDER BY etm.final_status_timestamp::DATE DESC
{% endif %}
),
aggregated_with_summary AS(
SELECT
'ИТОГО' AS day_or_hour,
SUM(trans_count) AS trans_count,
SUM(success_count) AS success_count,
SUM(sum_success_amount) AS sum_success_amount,
AVG(avg_success_amount) AS avg_success_amount,
ROUND(AVG(avg_success_time))::INTEGER AS avg_success_time,
ROUND(AVG(avg_reject_time))::INTEGER AS avg_reject_time,
SUM(appeal_count) AS appeal_count
FROM aggregated_data
UNION ALL
SELECT *
FROM aggregated_data
)
SELECT
day_or_hour,
trans_count,
success_count,
ROUND(success_count::DECIMAL / trans_count * 100, 2) AS conversion,
ROUND(sum_success_amount) AS sum_success_amount,
ROUND(avg_success_amount) AS avg_success_amount,
LPAD((avg_success_time / 3600)::TEXT, 2, '0')
|| ':' || LPAD((avg_success_time % 3600 / 60)::TEXT, 2, '0')
|| ':' || LPAD((avg_success_time % 60)::TEXT, 2, '0') AS
avg_success_time,
LPAD((avg_reject_time / 3600)::TEXT, 2, '0')
|| ':' || LPAD((avg_reject_time % 3600 / 60)::TEXT, 2, '0')
|| ':' || LPAD((avg_reject_time % 60)::TEXT, 2, '0') AS
avg_reject_time,
ROUND(appeal_count::DECIMAL / trans_count * 100, 2) AS appeal_percent
FROM aggregated_with_summary;
```
### Checklist
- [x] I have searched Superset docs and Slack and didn't find a solution to
my problem.
- [x] I have searched the GitHub issue tracker and didn't find a similar bug
report.
- [x] I have checked Superset's logs for errors and if I found a relevant
Python stacktrace, I included it here as text in the "additional context"
section.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscr...@superset.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscr...@superset.apache.org
For additional commands, e-mail: notifications-h...@superset.apache.org
