Hello
I am trying to run this query
SELECT subquery.created_at::date,
COUNT(*) FILTER (WHERE total_sales =0 ) AS "Zero_sales",
COUNT(*) FILTER (WHERE total_sales BETWEEN 1 AND 100) AS "1_to_100_sales",
COUNT(*) FILTER (WHERE total_sales BETWEEN 101 AND 200) AS "101_to_200_sales",
COUNT(*) FILTER (WHERE total_sales BETWEEN 201 AND 300) AS "201_to_300_sales",
COUNT(*) FILTER (WHERE total_sales BETWEEN 301 AND 400) AS "301_to_400_sales",
COUNT(*) FILTER (WHERE total_sales BETWEEN 401 AND 500) AS "401_to_500_sales",
COUNT(*) FILTER (WHERE total_sales > 500) AS "more_than_500_sales"
FROM
(
WITH date_series AS (
SELECT generate_series({{Start}}::date, {{End}}::date, interval '1 day')::date AS created_at
)
SELECT
m.merchant_id,
ds.created_at,
COALESCE(SUM(mp.amount), 0) AS total_sales
FROM
dev.merchant m
CROSS JOIN date_series ds
LEFT JOIN dev.mada_payment mp ON m.merchant_id = mp.merchant_id AND ds.created_at = mp.created_at::date
LEFT JOIN dev.users u ON m.merchant_user = u.user_id
WHERE
u.distributor_id = 1
AND u.status = 'ACTIVE'
and u.serial_number LIKE 'N7%'
and m.created_at < $2
GROUP BY
m.merchant_id,
ds.created_at
ORDER BY
m.merchant_id,
ds.created_at) as subquery
GROUP BY subquery.created_at::date
ORDER BY subquery.created_at::date;
WITH date_series AS (
SELECT generate_series(date '2024-09-01', date '2024-09-09', interval '1 day')::date AS created_at
),
sales_data AS (
SELECT
m.merchant_id,
ds.created_at,
COALESCE(SUM(mp.amount), 0) AS total_sales
FROM
dev.merchant m
CROSS JOIN date_series ds
LEFT JOIN dev.mada_payment mp ON m.merchant_id = mp.merchant_id AND ds.created_at = mp.created_at::date
LEFT JOIN dev.users u ON m.merchant_user = u.user_id
WHERE
u.distributor_id = 1
AND u.status = 'ACTIVE'
and u.serial_number LIKE 'N7%'
GROUP BY
m.merchant_id,
ds.created_at
)
SELECT
sd.created_at::date,
COUNT(*) FILTER (WHERE sd.total_sales = 0) AS "Zero_sales",
COUNT(*) FILTER (WHERE sd.total_sales BETWEEN 1 AND 100) AS "1_to_100_sales",
COUNT(*) FILTER (WHERE sd.total_sales BETWEEN 101 AND 200) AS "101_to_200_sales",
COUNT(*) FILTER (WHERE sd.total_sales BETWEEN 201 AND 300) AS "201_to_300_sales",
COUNT(*) FILTER (WHERE sd.total_sales BETWEEN 301 AND 400) AS "301_to_400_sales",
COUNT(*) FILTER (WHERE sd.total_sales BETWEEN 401 AND 500) AS "401_to_500_sales",
COUNT(*) FILTER (WHERE sd.total_sales > 500) AS "more_than_500_sales"
FROM
sales_data sd
GROUP BY
sd.created_at::date
ORDER BY
sd.created_at::date;
-- Define the date range parameters
WITH date_series AS (
SELECT generate_series($1::date, $2::date, interval '1 day')::date AS created_at
)
SELECT
subquery.created_at::date,
COUNT(*) FILTER (WHERE total_sales = 0) AS "Zero_sales",
COUNT(*) FILTER (WHERE total_sales BETWEEN 1 AND 100) AS "1_to_100_sales",
COUNT(*) FILTER (WHERE total_sales BETWEEN 101 AND 200) AS "101_to_200_sales",
COUNT(*) FILTER (WHERE total_sales BETWEEN 201 AND 300) AS "201_to_300_sales",
COUNT(*) FILTER (WHERE total_sales BETWEEN 301 AND 400) AS "301_to_400_sales",
COUNT(*) FILTER (WHERE total_sales BETWEEN 401 AND 500) AS "401_to_500_sales",
COUNT(*) FILTER (WHERE total_sales > 500) AS "more_than_500_sales"
FROM (
SELECT
m.merchant_id,
ds.created_at,
COALESCE(SUM(mp.amount), 0) AS total_sales
FROM
dev.merchant m
CROSS JOIN date_series ds
LEFT JOIN dev.mada_payment mp ON m.merchant_id = mp.merchant_id AND ds.created_at = mp.created_at::date
LEFT JOIN dev.users u ON m.merchant_user = u.user_id
WHERE
u.distributor_id = 1
AND u.status = 'ACTIVE'
AND u.serial_number LIKE 'N7%'
GROUP BY
m.merchant_id,
ds.created_at
ORDER BY
m.merchant_id,
ds.created_at
) AS subquery
GROUP BY
subquery.created_at::date
ORDER BY
subquery.created_at::date;
WITH date_series AS (
SELECT generate_series($1::date, $2::date, interval '1 day')::date AS created_at
)
SELECT
subquery.created_at::date,
COUNT(*) FILTER (WHERE total_sales = 0) AS "Zero_sales",
COUNT(*) FILTER (WHERE total_sales BETWEEN 1 AND 100) AS "1_to_100_sales",
COUNT(*) FILTER (WHERE total_sales BETWEEN 101 AND 200) AS "101_to_200_sales",
COUNT(*) FILTER (WHERE total_sales BETWEEN 201 AND 300) AS "201_to_300_sales",
COUNT(*) FILTER (WHERE total_sales BETWEEN 301 AND 400) AS "301_to_400_sales",
COUNT(*) FILTER (WHERE total_sales BETWEEN 401 AND 500) AS "401_to_500_sales",
COUNT(*) FILTER (WHERE total_sales > 500) AS "more_than_500_sales"
FROM (
SELECT
m.merchant_id,
ds.created_at,
COALESCE(SUM(mp.amount), 0) AS total_sales
FROM
dev.merchant m
CROSS JOIN date_series ds
LEFT JOIN dev.mada_payment mp ON m.merchant_id = mp.merchant_id AND ds.created_at = mp.created_at::date
LEFT JOIN dev.users u ON m.merchant_user = u.user_id
WHERE
u.distributor_id = 1
AND u.status = 'ACTIVE'
AND u.serial_number LIKE 'N7%'
AND CAST(mp.created_at AS date) BETWEEN $1 AND $2
GROUP BY
m.merchant_id,
ds.created_at
ORDER BY
m.merchant_id,
ds.created_at
) AS subquery
GROUP BY
subquery.created_at::date
ORDER BY
subquery.created_at::date;
and getting always error
and here is Log
[89067cba-aac1-4ee0-8a30-78385bfa8ecb] 2024-09-09T10:26:10+03:00 ERROR metabase.query-processor.middleware.catch-exceptions Error processing query: This connection has been closed.
{:database_id 33,
:started_at #t "2024-09-09T10:26:10.109598267+03:00[Asia/Riyadh]",
:via
[{:status :failed,
:class clojure.lang.ExceptionInfo,
:error "Error executing query: This connection has been closed.",
:stacktrace
["--> driver.sql_jdbc.execute$execute_reducible_query$fn__81498$fn__81499.invoke(execute.clj:716)"
"driver.sql_jdbc.execute$execute_reducible_query$fn__81498.invoke(execute.clj:713)"
"driver.sql_jdbc.execute$fn__81291$fn__81292.invoke(execute.clj:397)"
"driver.sql_jdbc.execute$do_with_resolved_connection.invokeStatic(execute.clj:337)"
"driver.sql_jdbc.execute$do_with_resolved_connection.invoke(execute.clj:320)"
"driver.sql_jdbc.execute$fn__81291.invokeStatic(execute.clj:391)"
"driver.sql_jdbc.execute$fn__81291.invoke(execute.clj:389)"
"driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:707)"
"driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:693)"
"driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:704)"
"driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:693)"
"driver.sql_jdbc$fn__108990.invokeStatic(sql_jdbc.clj:78)"
"driver.sql_jdbc$fn__108990.invoke(sql_jdbc.clj:76)"
"query_processor.pipeline$_STAR_execute_STAR_.invokeStatic(pipeline.clj:47)"
"query_processor.pipeline$_STAR_execute_STAR_.invoke(pipeline.clj:34)"
"query_processor.pipeline$_STAR_run_STAR_.invokeStatic(pipeline.clj:97)"
"query_processor.pipeline$_STAR_run_STAR_.invoke(pipeline.clj:90)"
"query_processor.execute$run.invokeStatic(execute.clj:61)"
"query_processor.execute$run.invoke(execute.clj:55)"
"query_processor.execute$add_native_form_to_result_metadata$fn__69760.invoke(execute.clj:24)"
"query_processor.execute$add_preprocessed_query_to_result_metadata_for_userland_query$fn__69765.invoke(execute.clj:35)"
"query_processor.middleware.cache$maybe_return_cached_results$maybe_return_cached_results_STAR___69751.invoke(cache.clj:242)"
"query_processor.middleware.permissions$check_query_permissions$fn__63761.invoke(permissions.clj:118)"
"query_processor.middleware.enterprise$check_download_permissions_middleware$fn__64359.invoke(enterprise.clj:51)"
"query_processor.middleware.enterprise$maybe_apply_column_level_perms_check_middleware$fn__64369.invoke(enterprise.clj:64)"
"query_processor.execute$execute$fn__69792.invoke(execute.clj:93)"
"query_processor.setup$do_with_qp_setup.invokeStatic(setup.clj:225)"
"query_processor.setup$do_with_qp_setup.invoke(setup.clj:216)"
"query_processor.execute$execute.invokeStatic(execute.clj:92)"
"query_processor.execute$execute.invoke(execute.clj:88)"
"query_processor$process_query_STAR__STAR_.invokeStatic(query_processor.clj:47)"
"query_processor$process_query_STAR__STAR_.invoke(query_processor.clj:43)"
"query_processor.middleware.enterprise$fn__64386$handle_audit_app_internal_queries__64387$fn__64389.invoke(enterprise.clj:96)"
"query_processor.middleware.enterprise$handle_audit_app_internal_queries_middleware$fn__64397.invoke(enterprise.clj:103)"
"query_processor.middleware.process_userland_query$process_userland_query_middleware$fn__75610.invoke(process_userland_query.clj:182)"
"query_processor.middleware.catch_exceptions$catch_exceptions$fn__75679.invoke(catch_exceptions.clj:128)"
"query_processor$process_query$fn__75716.invoke(query_processor.clj:78)"
"query_processor.setup$do_with_canceled_chan$fn__64801.invoke(setup.clj:189)"
"query_processor.setup$do_with_database_local_settings$fn__64796.invoke(setup.clj:181)"
"query_processor.setup$do_with_driver$fn__64791$fn__64792.invoke(setup.clj:166)"
"driver$do_with_driver.invokeStatic(driver.clj:104)"
"driver$do_with_driver.invoke(driver.clj:99)"
"query_processor.setup$do_with_driver$fn__64791.invoke(setup.clj:165)"
"query_processor.setup$do_with_metadata_provider$fn__64784$fn__64787.invoke(setup.clj:151)"
"query_processor.store$do_with_metadata_provider.invokeStatic(store.clj:171)"
"query_processor.store$do_with_metadata_provider.invoke(store.clj:151)"
"query_processor.store$do_with_metadata_provider.invokeStatic(store.clj:160)"
"query_processor.store$do_with_metadata_provider.invoke(store.clj:151)"
"query_processor.setup$do_with_metadata_provider$fn__64784.invoke(setup.clj:150)"
"query_processor.setup$do_with_resolved_database$fn__64778.invoke(setup.clj:128)"
"query_processor.setup$do_with_qp_setup.invokeStatic(setup.clj:232)"
"query_processor.setup$do_with_qp_setup.invoke(setup.clj:216)"
"query_processor$process_query.invokeStatic(query_processor.clj:76)"
"query_processor$process_query.invoke(query_processor.clj:69)"
"query_processor.card$process_query_for_card_default_qp.invokeStatic(card.clj:170)"
"query_processor.card$process_query_for_card_default_qp.invoke(card.clj:166)"
"query_processor.card$process_query_for_card_default_run_fn$fn__81687$fn__81688.invoke(card.clj:177)"
"query_processor.streaming$_streaming_response$fn__68235$fn__68236$fn__68237.invoke(streaming.clj:175)"
"query_processor.streaming$_streaming_response$fn__68235$fn__68236.invoke(streaming.clj:174)"
"query_processor.streaming$do_with_streaming_rff.invokeStatic(streaming.clj:165)"
"query_processor.streaming$do_with_streaming_rff.invoke(streaming.clj:152)"
"query_processor.streaming$_streaming_response$fn__68235.invoke(streaming.clj:171)"
"async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:68)"
"async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:66)"
"async.streaming_response$do_f_async$task__52165.invoke(streaming_response.clj:87)"],
:error_type :invalid-query,
:ex-data
{:driver :postgres,
:sql
["-- Metabase:: userID: 33 queryType: native queryHash: c0bd38bec44240b5e8ca0edd805148063dfbc3574064f48bf7264d4c248853a4"
"SELECT"
" subquery.created_at :: date,"
" COUNT(*) FILTER ("
" WHERE"
" total_sales = 0"
" ) AS \"Zero_sales\","
" COUNT(*) FILTER ("
" WHERE"
" total_sales BETWEEN 1 AND 100"
" ) AS \"1_to_100_sales\","
" COUNT(*) FILTER ("
" WHERE"
" total_sales BETWEEN 101 AND 200"
" ) AS \"101_to_200_sales\","
" COUNT(*) FILTER ("
" WHERE"
" total_sales BETWEEN 201 AND 300"
" ) AS \"201_to_300_sales\","
" COUNT(*) FILTER ("
" WHERE"
" total_sales BETWEEN 301 AND 400"
" ) AS \"301_to_400_sales\","
" COUNT(*) FILTER ("
" WHERE"
" total_sales BETWEEN 401 AND 500"
" ) AS \"401_to_500_sales\","
" COUNT(*) FILTER ("
" WHERE"
" total_sales > 500"
" ) AS \"more_than_500_sales\""
"FROM"
" ("
" WITH date_series AS ("
" SELECT"
" generate_series(? :: date, ? :: date, interval '1 day') :: date AS created_at"
" )"
" SELECT"
" m.merchant_id,"
" ds.created_at,"
" COALESCE(SUM(mp.amount), 0) AS total_sales"
" FROM"
" dev.merchant m"
" CROSS JOIN date_series ds"
" LEFT JOIN dev.mada_payment mp ON m.merchant_id = mp.merchant_id"
" AND ds.created_at = mp.created_at :: date"
" LEFT JOIN dev.users u ON m.merchant_user = u.user_id"
" WHERE"
" u.distributor_id = 1"
" AND u.status = 'ACTIVE'"
" and u.serial_number LIKE 'N7%'"
" and m.created_at < $2"
" GROUP BY"
" m.merchant_id,"
" ds.created_at"
" ORDER BY"
" m.merchant_id,"
" ds.created_at"
" ) as subquery"
"GROUP BY"
" subquery.created_at :: date"
"ORDER BY"
" subquery.created_at :: date;"
"WITH date_series AS ("
" SELECT"
" generate_series("
" date '2024-09-01',"
" date '2024-09-09',"
" interval '1 day'"
" ) :: date AS created_at"
"),"
"sales_data AS ("
" SELECT"
" m.merchant_id,"
" ds.created_at,"
" COALESCE(SUM(mp.amount), 0) AS total_sales"
" FROM"
" dev.merchant m"
" CROSS JOIN date_series ds"
" LEFT JOIN dev.mada_payment mp ON m.merchant_id = mp.merchant_id"
" AND ds.created_at = mp.created_at :: date"
" LEFT JOIN dev.users u ON m.merchant_user = u.user_id"
" WHERE"
" u.distributor_id = 1"
" AND u.status = 'ACTIVE'"
" and u.serial_number LIKE 'N7%'"
" GROUP BY"
" m.merchant_id,"
" ds.created_at"
")"
"SELECT"
" sd.created_at :: date,"
" COUNT(*) FILTER ("
" WHERE"
" sd.total_sales = 0"
" ) AS \"Zero_sales\","
" COUNT(*) FILTER ("
" WHERE"
" sd.total_sales BETWEEN 1 AND 100"
" ) AS \"1_to_100_sales\","
" COUNT(*) FILTER ("
" WHERE"
" sd.total_sales BETWEEN 101 AND 200"
" ) AS \"101_to_200_sales\","
" COUNT(*) FILTER ("
" WHERE"
" sd.total_sales BETWEEN 201 AND 300"
" ) AS \"201_to_300_sales\","
" COUNT(*) FILTER ("
" WHERE"
" sd.total_sales BETWEEN 301 AND 400"
" ) AS \"301_to_400_sales\","
" COUNT(*) FILTER ("
" WHERE"
" sd.total_sales BETWEEN 401 AND 500"
" ) AS \"401_to_500_sales\","
" COUNT(*) FILTER ("
" WHERE"
" sd.total_sales > 500"
" ) AS \"more_than_500_sales\""
"FROM"
" sales_data sd"
"GROUP BY"
" sd.created_at :: date"
"ORDER BY"
" sd.created_at :: date;"
"-- Define the date range parameters"
"WITH date_series AS ("
" SELECT"
" generate_series($1 :: date, $2 :: date, interval '1 day') :: date AS created_at"
")"
"SELECT"
" subquery.created_at :: date,"
" COUNT(*) FILTER ("
" WHERE"
" total_sales = 0"
" ) AS \"Zero_sales\","
" COUNT(*) FILTER ("
" WHERE"
" total_sales BETWEEN 1 AND 100"
" ) AS \"1_to_100_sales\","
" COUNT(*) FILTER ("
" WHERE"
" total_sales BETWEEN 101 AND 200"
" ) AS \"101_to_200_sales\","
" COUNT(*) FILTER ("
" WHERE"
" total_sales BETWEEN 201 AND 300"
" ) AS \"201_to_300_sales\","
" COUNT(*) FILTER ("
" WHERE"
" total_sales BETWEEN 301 AND 400"
" ) AS \"301_to_400_sales\","
" COUNT(*) FILTER ("
" WHERE"
" total_sales BETWEEN 401 AND 500"
" ) AS \"401_to_500_sales\","
" COUNT(*) FILTER ("
" WHERE"
" total_sales > 500"
" ) AS \"more_than_500_sales\""
"FROM"
" ("
" SELECT"
" m.merchant_id,"
" ds.created_at,"
" COALESCE(SUM(mp.amount), 0) AS total_sales"
" FROM"
" dev.merchant m"
" CROSS JOIN date_series ds"
" LEFT JOIN dev.mada_payment mp ON m.merchant_id = mp.merchant_id"
" AND ds.created_at = mp.created_at :: date"
" LEFT JOIN dev.users u ON m.merchant_user = u.user_id"
" WHERE"
" u.distributor_id = 1"
" AND u.status = 'ACTIVE'"
" AND u.serial_number LIKE 'N7%'"
" GROUP BY"
" m.merchant_id,"
" ds.created_at"
" ORDER BY"
" m.merchant_id,"
" ds.created_at"
" ) AS subquery"
"GROUP BY"
" subquery.created_at :: date"
"ORDER BY"
" subquery.created_at :: date;"
"WITH date_series AS ("
" SELECT"
" generate_series($1 :: date, $2 :: date, interval '1 day') :: date AS created_at"
")"
"SELECT"
" subquery.created_at :: date,"
" COUNT(*) FILTER ("
" WHERE"
" total_sales = 0"
" ) AS \"Zero_sales\","
" COUNT(*) FILTER ("
" WHERE"
" total_sales BETWEEN 1 AND 100"
" ) AS \"1_to_100_sales\","
" COUNT(*) FILTER ("
" WHERE"
" total_sales BETWEEN 101 AND 200"
" ) AS \"101_to_200_sales\","
" COUNT(*) FILTER ("
" WHERE"
" total_sales BETWEEN 201 AND 300"
" ) AS \"201_to_300_sales\","
" COUNT(*) FILTER ("
" WHERE"
" total_sales BETWEEN 301 AND 400"
" ) AS \"301_to_400_sales\","
" COUNT(*) FILTER ("
" WHERE"
" total_sales BETWEEN 401 AND 500"
" ) AS \"401_to_500_sales\","
" COUNT(*) FILTER ("
" WHERE"
" total_sales > 500"
" ) AS \"more_than_500_sales\""
"FROM"
" ("
" SELECT"
" m.merchant_id,"
" ds.created_at,"
" COALESCE(SUM(mp.amount), 0) AS total_sales"
" FROM"
" dev.merchant m"
" CROSS JOIN date_series ds"
" LEFT JOIN dev.mada_payment mp ON m.merchant_id = mp.merchant_id"
" AND ds.created_at = mp.created_at :: date"
" LEFT JOIN dev.users u ON m.merchant_user = u.user_id"
" WHERE"
" u.distributor_id = 1"
" AND u.status = 'ACTIVE'"
" AND u.serial_number LIKE 'N7%'"
" AND CAST(mp.created_at AS date) BETWEEN $1 AND $2"
" GROUP BY"
" m.merchant_id,"
" ds.created_at"
" ORDER BY"
" m.merchant_id,"
" ds.created_at"
" ) AS subquery"
"GROUP BY"
" subquery.created_at :: date"
"ORDER BY"
" subquery.created_at :: date;"],
:params [#t "2024-08-01" #t "2024-08-31"],
:type :invalid-query}}],
:action_id nil,
:state "08003",
:error_type :invalid-query,
:json_query
{:constraints {:max-results 10000, :max-results-bare-rows 2000},
:type :native,
:middleware
{:js-int-to-string? true, :ignore-cached-results? false, :process-viz-settings? false, :userland-query? true},
:cache-strategy nil,
:native
{:template-tags
{"Start"
{:type :date,
:name "Start",
:id "dda228e4-2b13-4902-b9a5-3a743e63916d",
:display-name "Start",
:default nil,
:widget-type nil},
"End"
{:type :date,
:name "End",
:id "e24c8a63-9307-488b-b01a-4fe67ecbfc77",
:display-name "End",
:default nil,
:widget-type nil}},
:query
"SELECT subquery.created_at::date,\r\n\t\tCOUNT(*) FILTER (WHERE total_sales =0 ) AS \"Zero_sales\",\r\n COUNT(*) FILTER (WHERE total_sales BETWEEN 1 AND 100) AS \"1_to_100_sales\",\r\n COUNT(*) FILTER (WHERE total_sales BETWEEN 101 AND 200) AS \"101_to_200_sales\",\r\n COUNT(*) FILTER (WHERE total_sales BETWEEN 201 AND 300) AS \"201_to_300_sales\",\r\n COUNT(*) FILTER (WHERE total_sales BETWEEN 301 AND 400) AS \"301_to_400_sales\",\r\n COUNT(*) FILTER (WHERE total_sales BETWEEN 401 AND 500) AS \"401_to_500_sales\",\r\n COUNT(*) FILTER (WHERE total_sales > 500) AS \"more_than_500_sales\"\r\nFROM\r\n\r\n(\r\nWITH date_series AS (\r\n SELECT generate_series({{Start}}::date, {{End}}::date, interval '1 day')::date AS created_at\r\n)\r\nSELECT\r\n m.merchant_id,\r\n ds.created_at,\r\n COALESCE(SUM(mp.amount), 0) AS total_sales\r\nFROM\r\n dev.merchant m\r\n CROSS JOIN date_series ds\r\n LEFT JOIN dev.mada_payment mp ON m.merchant_id = mp.merchant_id AND ds.created_at = mp.created_at::date\r\n LEFT JOIN dev.users u ON m.merchant_user = u.user_id\r\nWHERE\r\n\r\n u.distributor_id = 1\r\n AND u.status = 'ACTIVE'\r\n \t and u.serial_number LIKE 'N7%'\r\n and m.created_at < $2\r\nGROUP BY\r\n m.merchant_id,\r\n ds.created_at\r\nORDER BY\r\n m.merchant_id,\r\n ds.created_at) as subquery\r\nGROUP BY subquery.created_at::date\r\nORDER BY subquery.created_at::date;\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\nWITH date_series AS (\r\n SELECT generate_series(date '2024-09-01', date '2024-09-09', interval '1 day')::date AS created_at\r\n),\r\nsales_data AS (\r\n SELECT\r\n m.merchant_id,\r\n ds.created_at,\r\n COALESCE(SUM(mp.amount), 0) AS total_sales\r\n FROM\r\n dev.merchant m\r\n CROSS JOIN date_series ds\r\n LEFT JOIN dev.mada_payment mp ON m.merchant_id = mp.merchant_id AND ds.created_at = mp.created_at::date\r\n LEFT JOIN dev.users u ON m.merchant_user = u.user_id\r\n WHERE\r\n u.distributor_id = 1\r\n AND u.status = 'ACTIVE'\r\n and u.serial_number LIKE 'N7%'\r\n \r\n GROUP BY\r\n m.merchant_id,\r\n ds.created_at\r\n)\r\nSELECT\r\n sd.created_at::date,\r\n COUNT(*) FILTER (WHERE sd.total_sales = 0) AS \"Zero_sales\",\r\n COUNT(*) FILTER (WHERE sd.total_sales BETWEEN 1 AND 100) AS \"1_to_100_sales\",\r\n COUNT(*) FILTER (WHERE sd.total_sales BETWEEN 101 AND 200) AS \"101_to_200_sales\",\r\n COUNT(*) FILTER (WHERE sd.total_sales BETWEEN 201 AND 300) AS \"201_to_300_sales\",\r\n COUNT(*) FILTER (WHERE sd.total_sales BETWEEN 301 AND 400) AS \"301_to_400_sales\",\r\n COUNT(*) FILTER (WHERE sd.total_sales BETWEEN 401 AND 500) AS \"401_to_500_sales\",\r\n COUNT(*) FILTER (WHERE sd.total_sales > 500) AS \"more_than_500_sales\"\r\nFROM\r\n sales_data sd\r\nGROUP BY\r\n sd.created_at::date\r\nORDER BY\r\n sd.created_at::date;\r\n \r\n \r\n \r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n-- Define the date range parameters\r\nWITH date_series AS (\r\n SELECT generate_series($1::date, $2::date, interval '1 day')::date AS created_at\r\n)\r\nSELECT\r\n subquery.created_at::date,\r\n COUNT(*) FILTER (WHERE total_sales = 0) AS \"Zero_sales\",\r\n COUNT(*) FILTER (WHERE total_sales BETWEEN 1 AND 100) AS \"1_to_100_sales\",\r\n COUNT(*) FILTER (WHERE total_sales BETWEEN 101 AND 200) AS \"101_to_200_sales\",\r\n COUNT(*) FILTER (WHERE total_sales BETWEEN 201 AND 300) AS \"201_to_300_sales\",\r\n COUNT(*) FILTER (WHERE total_sales BETWEEN 301 AND 400) AS \"301_to_400_sales\",\r\n COUNT(*) FILTER (WHERE total_sales BETWEEN 401 AND 500) AS \"401_to_500_sales\",\r\n COUNT(*) FILTER (WHERE total_sales > 500) AS \"more_than_500_sales\"\r\nFROM (\r\n SELECT\r\n m.merchant_id,\r\n ds.created_at,\r\n COALESCE(SUM(mp.amount), 0) AS total_sales\r\n FROM\r\n dev.merchant m\r\n CROSS JOIN date_series ds\r\n LEFT JOIN dev.mada_payment mp ON m.merchant_id = mp.merchant_id AND ds.created_at = mp.created_at::date\r\n LEFT JOIN dev.users u ON m.merchant_user = u.user_id\r\n WHERE\r\n u.distributor_id = 1\r\n AND u.status = 'ACTIVE'\r\n AND u.serial_number LIKE 'N7%'\r\n GROUP BY\r\n m.merchant_id,\r\n ds.created_at\r\n ORDER BY\r\n m.merchant_id,\r\n ds.created_at\r\n) AS subquery\r\nGROUP BY\r\n subquery.created_at::date\r\nORDER BY\r\n subquery.created_at::date;\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\nWITH date_series AS (\r\n SELECT generate_series($1::date, $2::date, interval '1 day')::date AS created_at\r\n)\r\nSELECT\r\n subquery.created_at::date,\r\n COUNT(*) FILTER (WHERE total_sales = 0) AS \"Zero_sales\",\r\n COUNT(*) FILTER (WHERE total_sales BETWEEN 1 AND 100) AS \"1_to_100_sales\",\r\n COUNT(*) FILTER (WHERE total_sales BETWEEN 101 AND 200) AS \"101_to_200_sales\",\r\n COUNT(*) FILTER (WHERE total_sales BETWEEN 201 AND 300) AS \"201_to_300_sales\",\r\n COUNT(*) FILTER (WHERE total_sales BETWEEN 301 AND 400) AS \"301_to_400_sales\",\r\n COUNT(*) FILTER (WHERE total_sales BETWEEN 401 AND 500) AS \"401_to_500_sales\",\r\n COUNT(*) FILTER (WHERE total_sales > 500) AS \"more_than_500_sales\"\r\nFROM (\r\n SELECT\r\n m.merchant_id,\r\n ds.created_at,\r\n COALESCE(SUM(mp.amount), 0) AS total_sales\r\n FROM\r\n dev.merchant m\r\n CROSS JOIN date_series ds\r\n LEFT JOIN dev.mada_payment mp ON m.merchant_id = mp.merchant_id AND ds.created_at = mp.created_at::date\r\n LEFT JOIN dev.users u ON m.merchant_user = u.user_id\r\n WHERE\r\n u.distributor_id = 1\r\n AND u.status = 'ACTIVE'\r\n AND u.serial_number LIKE 'N7%'\r\n AND CAST(mp.created_at AS date) BETWEEN $1 AND $2\r\n GROUP BY\r\n m.merchant_id,\r\n ds.created_at\r\n ORDER BY\r\n m.merchant_id,\r\n ds.created_at\r\n) AS subquery\r\nGROUP BY\r\n subquery.created_at::date\r\nORDER BY\r\n subquery.created_at::date;"},
:viz-settings nil,
:database 33,
:parameters
[{:id "dda228e4-2b13-4902-b9a5-3a743e63916d",
:type "date/single",
:value "2024-08-01",
:target ["variable" ["template-tag" "Start"]]}
{:id "e24c8a63-9307-488b-b01a-4fe67ecbfc77",
:type "date/single",
:value "2024-08-31",
:target ["variable" ["template-tag" "End"]]}]},
:status :failed,
:class org.postgresql.util.PSQLException,
:stacktrace
["org.postgresql.jdbc.PgConnection.checkClosed(PgConnection.java:1009)"
"org.postgresql.jdbc.PgConnection.getAutoCommit(PgConnection.java:969)"
"org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:439)"
"org.postgresql.jdbc.PgStatement.execute(PgStatement.java:419)"
"org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:194)"
"org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:137)"
"com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:1471)"
"--> driver.sql_jdbc.execute$fn__81415.invokeStatic(execute.clj:565)"
"driver.sql_jdbc.execute$fn__81415.invoke(execute.clj:563)"
"driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invokeStatic(execute.clj:578)"
"driver.sql_jdbc.execute$execute_statement_or_prepared_statement_BANG_.invoke(execute.clj:574)"
"driver.sql_jdbc.execute$execute_reducible_query$fn__81498$fn__81499.invoke(execute.clj:714)"
"driver.sql_jdbc.execute$execute_reducible_query$fn__81498.invoke(execute.clj:713)"
"driver.sql_jdbc.execute$fn__81291$fn__81292.invoke(execute.clj:397)"
"driver.sql_jdbc.execute$do_with_resolved_connection.invokeStatic(execute.clj:337)"
"driver.sql_jdbc.execute$do_with_resolved_connection.invoke(execute.clj:320)"
"driver.sql_jdbc.execute$fn__81291.invokeStatic(execute.clj:391)"
"driver.sql_jdbc.execute$fn__81291.invoke(execute.clj:389)"
"driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:707)"
"driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:693)"
"driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:704)"
"driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:693)"
"driver.sql_jdbc$fn__108990.invokeStatic(sql_jdbc.clj:78)"
"driver.sql_jdbc$fn__108990.invoke(sql_jdbc.clj:76)"
"query_processor.pipeline$_STAR_execute_STAR_.invokeStatic(pipeline.clj:47)"
"query_processor.pipeline$_STAR_execute_STAR_.invoke(pipeline.clj:34)"
"query_processor.pipeline$_STAR_run_STAR_.invokeStatic(pipeline.clj:97)"
"query_processor.pipeline$_STAR_run_STAR_.invoke(pipeline.clj:90)"
"query_processor.execute$run.invokeStatic(execute.clj:61)"
"query_processor.execute$run.invoke(execute.clj:55)"
"query_processor.execute$add_native_form_to_result_metadata$fn__69760.invoke(execute.clj:24)"
"query_processor.execute$add_preprocessed_query_to_result_metadata_for_userland_query$fn__69765.invoke(execute.clj:35)"
"query_processor.middleware.cache$maybe_return_cached_results$maybe_return_cached_results_STAR___69751.invoke(cache.clj:242)"
"query_processor.middleware.permissions$check_query_permissions$fn__63761.invoke(permissions.clj:118)"
"query_processor.middleware.enterprise$check_download_permissions_middleware$fn__64359.invoke(enterprise.clj:51)"
"query_processor.middleware.enterprise$maybe_apply_column_level_perms_check_middleware$fn__64369.invoke(enterprise.clj:64)"
"query_processor.execute$execute$fn__69792.invoke(execute.clj:93)"
"query_processor.setup$do_with_qp_setup.invokeStatic(setup.clj:225)"
"query_processor.setup$do_with_qp_setup.invoke(setup.clj:216)"
"query_processor.execute$execute.invokeStatic(execute.clj:92)"
"query_processor.execute$execute.invoke(execute.clj:88)"
"query_processor$process_query_STAR__STAR_.invokeStatic(query_processor.clj:47)"
"query_processor$process_query_STAR__STAR_.invoke(query_processor.clj:43)"
"query_processor.middleware.enterprise$fn__64386$handle_audit_app_internal_queries__64387$fn__64389.invoke(enterprise.clj:96)"
"query_processor.middleware.enterprise$handle_audit_app_internal_queries_middleware$fn__64397.invoke(enterprise.clj:103)"
"query_processor.middleware.process_userland_query$process_userland_query_middleware$fn__75610.invoke(process_userland_query.clj:182)"
"query_processor.middleware.catch_exceptions$catch_exceptions$fn__75679.invoke(catch_exceptions.clj:128)"
"query_processor$process_query$fn__75716.invoke(query_processor.clj:78)"
"query_processor.setup$do_with_canceled_chan$fn__64801.invoke(setup.clj:189)"
"query_processor.setup$do_with_database_local_settings$fn__64796.invoke(setup.clj:181)"
"query_processor.setup$do_with_driver$fn__64791$fn__64792.invoke(setup.clj:166)"
"driver$do_with_driver.invokeStatic(driver.clj:104)"
"driver$do_with_driver.invoke(driver.clj:99)"
"query_processor.setup$do_with_driver$fn__64791.invoke(setup.clj:165)"
"query_processor.setup$do_with_metadata_provider$fn__64784$fn__64787.invoke(setup.clj:151)"
"query_processor.store$do_with_metadata_provider.invokeStatic(store.clj:171)"
"query_processor.store$do_with_metadata_provider.invoke(store.clj:151)"
"query_processor.store$do_with_metadata_provider.invokeStatic(store.clj:160)"
"query_processor.store$do_with_metadata_provider.invoke(store.clj:151)"
"query_processor.setup$do_with_metadata_provider$fn__64784.invoke(setup.clj:150)"
"query_processor.setup$do_with_resolved_database$fn__64778.invoke(setup.clj:128)"
"query_processor.setup$do_with_qp_setup.invokeStatic(setup.clj:232)"
"query_processor.setup$do_with_qp_setup.invoke(setup.clj:216)"
"query_processor$process_query.invokeStatic(query_processor.clj:76)"
"query_processor$process_query.invoke(query_processor.clj:69)"
"query_processor.card$process_query_for_card_default_qp.invokeStatic(card.clj:170)"
"query_processor.card$process_query_for_card_default_qp.invoke(card.clj:166)"
"query_processor.card$process_query_for_card_default_run_fn$fn__81687$fn__81688.invoke(card.clj:177)"
"query_processor.streaming$_streaming_response$fn__68235$fn__68236$fn__68237.invoke(streaming.clj:175)"
"query_processor.streaming$_streaming_response$fn__68235$fn__68236.invoke(streaming.clj:174)"
"query_processor.streaming$do_with_streaming_rff.invokeStatic(streaming.clj:165)"
"query_processor.streaming$do_with_streaming_rff.invoke(streaming.clj:152)"
"query_processor.streaming$_streaming_response$fn__68235.invoke(streaming.clj:171)"
"async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:68)"
"async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:66)"
"async.streaming_response$do_f_async$task__52165.invoke(streaming_response.clj:87)"],
:card_id 103,
:context :question,
:error "This connection has been closed.",
:row_count 0,
:running_time 0,
:data {:rows [], :cols []}}
my Diagnostic Info
{
"browser-info": {
"language": "en-US",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/128.0.0.0 Safari/537.36 Edg/128.0.0.0",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "17.0.12+7-Ubuntu-1ubuntu222.04",
"java.vendor": "Ubuntu",
"java.vendor.url": "https://ubuntu.com/",
"java.version": "17.0.12",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "17.0.12+7-Ubuntu-1ubuntu222.04",
"os.name": "Linux",
"os.version": "5.15.0-119-generic",
"user.language": "en",
"user.timezone": "Asia/Riyadh"
},
"metabase-info": {
"databases": [
"postgres"
],
"hosting-env": "unknown",
"application-database": "h2",
"application-database-details": {
"database": {
"name": "H2",
"version": "2.1.214 (2022-06-13)"
},
"jdbc-driver": {
"name": "H2 JDBC Driver",
"version": "2.1.214 (2022-06-13)"
}
},
"run-mode": "prod",
"version": {
"date": "2024-06-28",
"tag": "v0.50.8",
"hash": "dc9e68b"
},
"settings": {
"report-timezone": "Asia/Riyadh"
}
}
}