Hi, I’m unsure what causing this connection attempt failure. The error would gone after page refresh, some times it took several refresh, sometimes it absolutely fine. There should be no issue with connection to db because there are other services using the db and they are stable. I thought it was because of connection pool exhaustion so I’ve tried to increase the pool with env variable MB_JDBC_DATA_WAREHOUSE_MAX_CONNECTION_POOL_SIZE=30 . There should also be no DNS resolution issue because other service that using the same db is also hosted in the same VPS and the other service is stable. Any help is really appreciated
2026-Jul-01 09:06:22 2026-07-01 09:06:22,802 ERROR middleware.catch-exceptions :: Error processing query: The connection attempt failed.
2026-Jul-01 09:06:22 {:database_id 2,
2026-Jul-01 09:06:22 :parameterized false,
2026-Jul-01 09:06:22 :started_at #t "2026-07-01T09:06:12.764320085Z[GMT]",
2026-Jul-01 09:06:22 :via
2026-Jul-01 09:06:22 [{:status :failed,
2026-Jul-01 09:06:22 :class org.postgresql.util.PSQLException,
2026-Jul-01 09:06:22 :error "The connection attempt failed.",
2026-Jul-01 09:06:22 :stacktrace
2026-Jul-01 09:06:22 ["org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:385)"
2026-Jul-01 09:06:22 "org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:57)"
2026-Jul-01 09:06:22 "org.postgresql.jdbc.PgConnection.(PgConnection.java:279)"
2026-Jul-01 09:06:22 "org.postgresql.Driver.makeConnection(Driver.java:448)"
2026-Jul-01 09:06:22 "org.postgresql.Driver.connect(Driver.java:298)"
2026-Jul-01 09:06:22 "java.sql/java.sql.DriverManager.getConnection(Unknown Source)"
2026-Jul-01 09:06:22 "java.sql/java.sql.DriverManager.getConnection(Unknown Source)"
2026-Jul-01 09:06:22 "--> connection_pool$proxy_data_source$reify__42954.getConnection(connection_pool.clj:29)"],
2026-Jul-01 09:06:22 :state "08001"}
2026-Jul-01 09:06:22 {:status :failed,
2026-Jul-01 09:06:22 :class com.mchange.v2.resourcepool.CannotAcquireResourceException,
2026-Jul-01 09:06:22 :error "A ResourcePool could not acquire a resource from its primary factory or source.",
2026-Jul-01 09:06:22 :stacktrace
2026-Jul-01 09:06:22 ["com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1660)"
2026-Jul-01 09:06:22 "com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:710)"
2026-Jul-01 09:06:22 "com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:616)"
2026-Jul-01 09:06:22 "com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndScacheMarkConnectionInUse(C3P0PooledConnectionPool.java:966)"
2026-Jul-01 09:06:22 "com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:894)"
2026-Jul-01 09:06:22 "com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:105)"
2026-Jul-01 09:06:22 "--> driver.sql_jdbc.execute$do_with_resolved_connection$get_conn__93063.invoke(execute.clj:344)"
2026-Jul-01 09:06:22 "driver.sql_jdbc.execute$do_with_resolved_connection.invokeStatic(execute.clj:347)"
2026-Jul-01 09:06:22 "driver.sql_jdbc.execute$do_with_resolved_connection.invoke(execute.clj:328)"
2026-Jul-01 09:06:22 "driver.sql_jdbc.execute$fn__93136.invokeStatic(execute.clj:426)"
2026-Jul-01 09:06:22 "driver.sql_jdbc.execute$fn__93136.invoke(execute.clj:424)"
2026-Jul-01 09:06:22 "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:770)"
2026-Jul-01 09:06:22 "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:759)"
2026-Jul-01 09:06:22 "driver.sql_jdbc$fn__96050.invokeStatic(sql_jdbc.clj:87)"
2026-Jul-01 09:06:22 "driver.sql_jdbc$fn__96050.invoke(sql_jdbc.clj:85)"
2026-Jul-01 09:06:22 "query_processor.pipeline$_STAR_execute_STAR_.invokeStatic(pipeline.clj:59)"
2026-Jul-01 09:06:22 "query_processor.pipeline$_STAR_execute_STAR_.invoke(pipeline.clj:44)"
2026-Jul-01 09:06:22 "query_processor.pipeline$_STAR_run_STAR_.invokeStatic(pipeline.clj:119)"
2026-Jul-01 09:06:22 "query_processor.pipeline$_STAR_run_STAR_.invoke(pipeline.clj:108)"
2026-Jul-01 09:06:22 "query_processor.execute$run.invokeStatic(execute.clj:72)"
2026-Jul-01 09:06:22 "query_processor.execute$run.invoke(execute.clj:62)"
2026-Jul-01 09:06:22 "query_processor.middleware.enterprise$swap_destination_db_middleware$fn__73480.invoke(enterprise.clj:75)"
2026-Jul-01 09:06:22 "query_processor.middleware.enterprise$apply_impersonation_postprocessing_middleware$fn__73462.invoke(enterprise.clj:50)"
2026-Jul-01 09:06:22 "query_processor.middleware.update_used_cards$update_used_cards_BANG_$fn__76020.invoke(update_used_cards.clj:67)"
2026-Jul-01 09:06:22 "query_processor.execute$add_native_form_to_result_metadata$fn__76060.invoke(execute.clj:27)"
2026-Jul-01 09:06:22 "query_processor.execute$add_preprocessed_query_to_result_metadata_for_userland_query$fn__76067.invoke(execute.clj:39)"
2026-Jul-01 09:06:22 "query_processor.middleware.cache$run_query_with_cache.invokeStatic(cache.clj:221)"
2026-Jul-01 09:06:22 "query_processor.middleware.cache$run_query_with_cache.invoke(cache.clj:194)"
2026-Jul-01 09:06:22 "query_processor.middleware.cache$maybe_return_cached_results$maybe_return_cached_results_STAR___75986.invoke(cache.clj:247)"
2026-Jul-01 09:06:22 "query_processor.middleware.permissions$check_query_permissions$fn__72653.invoke(permissions.clj:153)"
2026-Jul-01 09:06:22 "query_processor.middleware.enterprise$check_download_permissions_middleware$fn__73490.invoke(enterprise.clj:92)"
2026-Jul-01 09:06:22 "query_processor.execute$execute$fn__76099.invoke(execute.clj:99)"
2026-Jul-01 09:06:22 "query_processor.setup$do_with_qp_setup.invokeStatic(setup.clj:232)"
2026-Jul-01 09:06:22 "query_processor.setup$do_with_qp_setup.invoke(setup.clj:223)"
2026-Jul-01 09:06:22 "query_processor.execute$execute.invokeStatic(execute.clj:98)"
2026-Jul-01 09:06:22 "query_processor.execute$execute.invoke(execute.clj:94)"
2026-Jul-01 09:06:22 "query_processor$process_query_STAR__STAR_.invokeStatic(query_processor.clj:50)"
2026-Jul-01 09:06:22 "query_processor$process_query_STAR__STAR_.invoke(query_processor.clj:45)"
2026-Jul-01 09:06:22 "query_processor.middleware.enterprise$fn__73507$handle_audit_app_internal_queries__73508$fn__73510.invoke(enterprise.clj:124)"
2026-Jul-01 09:06:22 "query_processor.middleware.enterprise$handle_audit_app_internal_queries_middleware$fn__73518.invoke(enterprise.clj:131)"
2026-Jul-01 09:06:22 "query_processor.middleware.process_userland_query$process_userland_query_middleware$fn__80995.invoke(process_userland_query.clj:185)"
2026-Jul-01 09:06:22 "query_processor.middleware.catch_exceptions$catch_exceptions$fn__81055.invoke(catch_exceptions.clj:139)"
2026-Jul-01 09:06:22 "query_processor$process_query$fn__81095.invoke(query_processor.clj:81)"
2026-Jul-01 09:06:22 "query_processor.setup$do_with_canceled_chan$fn__75389.invoke(setup.clj:194)"
2026-Jul-01 09:06:22 "query_processor.setup$do_with_database_local_settings$fn__75384.invoke(setup.clj:188)"
2026-Jul-01 09:06:22 "query_processor.setup$do_with_driver$fn__75375$fn__75376.invoke(setup.clj:173)"
2026-Jul-01 09:06:22 "driver$do_with_driver.invokeStatic(driver.clj:54)"
2026-Jul-01 09:06:22 "driver$do_with_driver.invoke(driver.clj:49)"
2026-Jul-01 09:06:22 "query_processor.setup$do_with_driver$fn__75375.invoke(setup.clj:172)"
2026-Jul-01 09:06:22 "query_processor.setup$do_with_metadata_provider$fn__75367.invoke(setup.clj:137)"
2026-Jul-01 09:06:22 "query_processor.setup$do_with_resolved_database$fn__75359.invoke(setup.clj:125)"
2026-Jul-01 09:06:22 "query_processor.setup$do_with_qp_setup.invokeStatic(setup.clj:240)"
2026-Jul-01 09:06:22 "query_processor.setup$do_with_qp_setup.invoke(setup.clj:223)"
2026-Jul-01 09:06:22 "query_processor$process_query.invokeStatic(query_processor.clj:79)"
2026-Jul-01 09:06:22 "query_processor$process_query.invoke(query_processor.clj:72)"
2026-Jul-01 09:06:22 "query_processor.card$process_query_for_card_default_qp.invokeStatic(card.clj:235)"
2026-Jul-01 09:06:22 "query_processor.card$process_query_for_card_default_qp.invoke(card.clj:231)"
2026-Jul-01 09:06:22 "query_processor.card$process_query_for_card_default_run_fn$fn__89846$fn__89847.invoke(card.clj:243)"
2026-Jul-01 09:06:22 "query_processor.streaming$_streaming_response$fn__86559$fn__86560$fn__86561.invoke(streaming.clj:225)"
2026-Jul-01 09:06:22 "query_processor.streaming$_streaming_response$fn__86559$fn__86560.invoke(streaming.clj:223)"
2026-Jul-01 09:06:22 "query_processor.streaming$do_with_streaming_rff.invokeStatic(streaming.clj:214)"
2026-Jul-01 09:06:22 "query_processor.streaming$do_with_streaming_rff.invoke(streaming.clj:201)"
2026-Jul-01 09:06:22 "query_processor.streaming$_streaming_response$fn__86559.invoke(streaming.clj:220)"
2026-Jul-01 09:06:22 "server.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:83)"
2026-Jul-01 09:06:22 "server.streaming_response$do_f_STAR_.invoke(streaming_response.clj:81)"
2026-Jul-01 09:06:22 "server.streaming_response$do_f_async$task__84295.invoke(streaming_response.clj:98)"]}
2026-Jul-01 09:06:22 {:status :failed,
2026-Jul-01 09:06:22 :class java.sql.SQLException,
2026-Jul-01 09:06:22 :error "Connections could not be acquired from the underlying database!",
2026-Jul-01 09:06:22 :stacktrace
2026-Jul-01 09:06:22 ["com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:117)"
2026-Jul-01 09:06:22 "com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:902)"
2026-Jul-01 09:06:22 "com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:105)"
2026-Jul-01 09:06:22 "--> driver.sql_jdbc.execute$do_with_resolved_connection$get_conn__93063.invoke(execute.clj:344)"
2026-Jul-01 09:06:22 "driver.sql_jdbc.execute$do_with_resolved_connection.invokeStatic(execute.clj:347)"
2026-Jul-01 09:06:22 "driver.sql_jdbc.execute$do_with_resolved_connection.invoke(execute.clj:328)"
2026-Jul-01 09:06:22 "driver.sql_jdbc.execute$fn__93136.invokeStatic(execute.clj:426)"
2026-Jul-01 09:06:22 "driver.sql_jdbc.execute$fn__93136.invoke(execute.clj:424)"
2026-Jul-01 09:06:22 "driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:770)"
2026-Jul-01 09:06:22 "driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:759)"
2026-Jul-01 09:06:22 "driver.sql_jdbc$fn__96050.invokeStatic(sql_jdbc.clj:87)"
2026-Jul-01 09:06:22 "driver.sql_jdbc$fn__96050.invoke(sql_jdbc.clj:85)"
2026-Jul-01 09:06:22 "query_processor.pipeline$_STAR_execute_STAR_.invokeStatic(pipeline.clj:59)"
2026-Jul-01 09:06:22 "query_processor.pipeline$_STAR_execute_STAR_.invoke(pipeline.clj:44)"
2026-Jul-01 09:06:22 "query_processor.pipeline$_STAR_run_STAR_.invokeStatic(pipeline.clj:119)"
2026-Jul-01 09:06:22 "query_processor.pipeline$_STAR_run_STAR_.invoke(pipeline.clj:108)"
2026-Jul-01 09:06:22 "query_processor.execute$run.invokeStatic(execute.clj:72)"
2026-Jul-01 09:06:22 "query_processor.execute$run.invoke(execute.clj:62)"
2026-Jul-01 09:06:22 "query_processor.middleware.enterprise$swap_destination_db_middleware$fn__73480.invoke(enterprise.clj:75)"
2026-Jul-01 09:06:22 "query_processor.middleware.enterprise$apply_impersonation_postprocessing_middleware$fn__73462.invoke(enterprise.clj:50)"
2026-Jul-01 09:06:22 "query_processor.middleware.update_used_cards$update_used_cards_BANG_$fn__76020.invoke(update_used_cards.clj:67)"
2026-Jul-01 09:06:22 "query_processor.execute$add_native_form_to_result_metadata$fn__76060.invoke(execute.clj:27)"
2026-Jul-01 09:06:22 "query_processor.execute$add_preprocessed_query_to_result_metadata_for_userland_query$fn__76067.invoke(execute.clj:39)"
2026-Jul-01 09:06:22 "query_processor.middleware.cache$run_query_with_cache.invokeStatic(cache.clj:221)"
2026-Jul-01 09:06:22 "query_processor.middleware.cache$run_query_with_cache.invoke(cache.clj:194)"
2026-Jul-01 09:06:22 "query_processor.middleware.cache$maybe_return_cached_results$maybe_return_cached_results_STAR___75986.invoke(cache.clj:247)"
2026-Jul-01 09:06:22 "query_processor.middleware.permissions$check_query_permissions$fn__72653.invoke(permissions.clj:153)"
2026-Jul-01 09:06:22 "query_processor.middleware.enterprise$check_download_permissions_middleware$fn__73490.invoke(enterprise.clj:92)"
2026-Jul-01 09:06:22 "query_processor.execute$execute$fn__76099.invoke(execute.clj:99)"
2026-Jul-01 09:06:22 "query_processor.setup$do_with_qp_setup.invokeStatic(setup.clj:232)"
2026-Jul-01 09:06:22 "query_processor.setup$do_with_qp_setup.invoke(setup.clj:223)"
2026-Jul-01 09:06:22 "query_processor.execute$execute.invokeStatic(execute.clj:98)"
2026-Jul-01 09:06:22 "query_processor.execute$execute.invoke(execute.clj:94)"
2026-Jul-01 09:06:22 "query_processor$process_query_STAR__STAR_.invokeStatic(query_processor.clj:50)"
2026-Jul-01 09:06:22 "query_processor$process_query_STAR__STAR_.invoke(query_processor.clj:45)"
2026-Jul-01 09:06:22 "query_processor.middleware.enterprise$fn__73507$handle_audit_app_internal_queries__73508$fn__73510.invoke(enterprise.clj:124)"
2026-Jul-01 09:06:22 "query_processor.middleware.enterprise$handle_audit_app_internal_queries_middleware$fn__73518.invoke(enterprise.clj:131)"
2026-Jul-01 09:06:22 "query_processor.middleware.process_userland_query$process_userland_query_middleware$fn__80995.invoke(process_userland_query.clj:185)"
2026-Jul-01 09:06:22 "query_processor.middleware.catch_exceptions$catch_exceptions$fn__81055.invoke(catch_exceptions.clj:139)"
2026-Jul-01 09:06:22 "query_processor$process_query$fn__81095.invoke(query_processor.clj:81)"
2026-Jul-01 09:06:22 "query_processor.setup$do_with_canceled_chan$fn__75389.invoke(setup.clj:194)"
2026-Jul-01 09:06:22 "query_processor.setup$do_with_database_local_settings$fn__75384.invoke(setup.clj:188)"
2026-Jul-01 09:06:22 "query_processor.setup$do_with_driver$fn__75375$fn__75376.invoke(setup.clj:173)"
2026-Jul-01 09:06:22 "driver$do_with_driver.invokeStatic(driver.clj:54)"
2026-Jul-01 09:06:22 "driver$do_with_driver.invoke(driver.clj:49)"
2026-Jul-01 09:06:22 "query_processor.setup$do_with_driver$fn__75375.invoke(setup.clj:172)"
2026-Jul-01 09:06:22 "query_processor.setup$do_with_metadata_provider$fn__75367.invoke(setup.clj:137)"
2026-Jul-01 09:06:22 "query_processor.setup$do_with_resolved_database$fn__75359.invoke(setup.clj:125)"
2026-Jul-01 09:06:22 "query_processor.setup$do_with_qp_setup.invokeStatic(setup.clj:240)"
2026-Jul-01 09:06:22 "query_processor.setup$do_with_qp_setup.invoke(setup.clj:223)"
2026-Jul-01 09:06:22 "query_processor$process_query.invokeStatic(query_processor.clj:79)"
2026-Jul-01 09:06:22 "query_processor$process_query.invoke(query_processor.clj:72)"
2026-Jul-01 09:06:22 "query_processor.card$process_query_for_card_default_qp.invokeStatic(card.clj:235)"
2026-Jul-01 09:06:22 "query_processor.card$process_query_for_card_default_qp.invoke(card.clj:231)"
2026-Jul-01 09:06:22 "query_processor.card$process_query_for_card_default_run_fn$fn__89846$fn__89847.invoke(card.clj:243)"
2026-Jul-01 09:06:22 "query_processor.streaming$_streaming_response$fn__86559$fn__86560$fn__86561.invoke(streaming.clj:225)"
2026-Jul-01 09:06:22 "query_processor.streaming$_streaming_response$fn__86559$fn__86560.invoke(streaming.clj:223)"
2026-Jul-01 09:06:22 "query_processor.streaming$do_with_streaming_rff.invokeStatic(streaming.clj:214)"
2026-Jul-01 09:06:22 "query_processor.streaming$do_with_streaming_rff.invoke(streaming.clj:201)"
2026-Jul-01 09:06:22 "query_processor.streaming$_streaming_response$fn__86559.invoke(streaming.clj:220)"
2026-Jul-01 09:06:22 "server.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:83)"
2026-Jul-01 09:06:22 "server.streaming_response$do_f_STAR_.invoke(streaming_response.clj:81)"
2026-Jul-01 09:06:22 "server.streaming_response$do_f_async$task__84295.invoke(streaming_response.clj:98)"],
2026-Jul-01 09:06:22 :state "08001"}],
2026-Jul-01 09:06:22 :action_id nil,
2026-Jul-01 09:06:22 :json_query
2026-Jul-01 09:06:22 {:constraints {:max-results 10000, :max-results-bare-rows 2000},
2026-Jul-01 09:06:22 :lib/type :mbql/query,
2026-Jul-01 09:06:22 :lib/metadata
2026-Jul-01 09:06:22 (metabase.lib.metadata.invocation-tracker/invocation-tracker-provider (metabase.lib.metadata.cached-provider/cached-metadata-provider (metabase.lib-be.metadata.jvm/->UncachedApplicationDatabaseMetadataProvider 2))),
2026-Jul-01 09:06:22 :stages
2026-Jul-01 09:06:22 [{:lib/type :mbql.stage/native,
2026-Jul-01 09:06:22 :native
2026-Jul-01 09:06:22 "-- ============================================================\n-- TUESDAY FREE SPINS — Early Reactivation\n-- Campaign: No-deposit free spins\n-- Window: Last real-money deposit AND last real-money bet\n-- both between 7 and 15 days ago\n-- Run: Every Monday evening / Tuesday morning\n--\n-- Performance approach:\n-- Step 1 → exclusion list (tiny)\n-- Step 2 → cheap MAX query to get candidate user_ids only\n-- Steps 3/4 → heavy aggregation scoped to those IDs only\n-- ============================================================\n\nWITH\n\n-- ────────────────────────────────────────────────────────────\n-- 1. HARD EXCLUSIONS\n-- Materialised once. Never surface these players.\n-- ────────────────────────────────────────────────────────────\nexcluded AS (\n SELECT id AS user_id FROM public.z_daily_ignored_users\n UNION\n SELECT ut.user_id\n FROM public.user_tags ut\n JOIN public.tags tg ON tg.id = ut.tag_id\n WHERE LOWER(tg.tag) = 'bonus abuser'\n),\n\n-- ────────────────────────────────────────────────────────────\n-- 2. CHURN CANDIDATES — user_ids only, cheap\n--\n-- Uses transactions directly (has user_id + ledger_id),\n-- so no wallets join needed here.\n-- Hits: transactions_user_id_idx + ledgers_purpose_idx\n--\n-- Only returns the small set whose LAST deposit\n-- fell in the 7–15 day window. All heavy aggregation\n-- below is scoped to this set.\n-- ────────────────────────────────────────────────────────────\nchurn_candidates AS (\n SELECT t.user_id\n FROM public.transactions t\n JOIN public.ledgers l\n ON l.id = t.ledger_id\n AND l.purpose = 'Deposit'\n WHERE t.status = 'completed'\n AND t.user_id NOT IN (SELECT user_id FROM excluded)\n GROUP BY t.user_id\n HAVING MAX(t.created_at)::date BETWEEN CURRENT_DATE - 15\n AND CURRENT_DATE - 7\n),\n\n-- ────────────────────────────────────────────────────────────\n-- 3. LIFETIME DEPOSIT STATS\n-- Heavy aggregation — but scoped to candidates only.\n-- Mirrors your existing deposit CTE pattern exactly.\n-- ────────────────────────────────────────────────────────────\ndeposit_stats AS (\n SELECT\n u.id AS user_id,\n u.username,\n u.email,\n COUNT(DISTINCT t.id) AS deposit_count,\n COALESCE(\n SUM(ABS(COALESCE(NULLIF(l.fiat_amount::text,'NaN')::numeric, 0))),\n 0\n ) AS total_deposit_usd,\n COALESCE(\n SUM(ABS(COALESCE(NULLIF(l.fiat_amount::text,'NaN')::numeric, 0)))\n / NULLIF(COUNT(DISTINCT t.id), 0),\n 0\n ) AS avg_deposit_usd,\n COALESCE(\n SUM(ABS(COALESCE(NULLIF(l.fiat_amount::text,'NaN')::numeric, 0)))\n FILTER (WHERE t.created_at >= CURRENT_DATE - 90),\n 0\n ) AS deposit_usd_last_90d,\n MIN(t.created_at) AS first_deposit_at,\n MAX(t.created_at) AS last_deposit_at\n\n FROM public.users u\n JOIN public.wallets w\n ON w.user_id = u.id\n JOIN public.ledgers l\n ON (l.to_wallet_id = w.id OR l.from_wallet_id = w.id)\n AND l.purpose = 'Deposit'\n JOIN public.transactions t\n ON t.ledger_id = l.id\n AND t.status = 'completed'\n WHERE u.id IN (SELECT user_id FROM churn_candidates) -- ← scoped\n GROUP BY u.id, u.username, u.email\n),\n\n-- ────────────────────────────────────────────────────────────\n-- 4. LIFETIME CASINO STATS\n-- Only calculated for churn candidates to keep it fast.\n-- Splits real vs bonus clearly.\n-- ────────────────────────────────────────────────────────────\ncasino_stats AS (\n SELECT\n ct.user_id,\n\n -- Real bets & wins\n COALESCE(SUM(ABS(COALESCE(NULLIF(l.fiat_amount::text,'NaN')::numeric, 0)))\n FILTER (WHERE l.purpose = 'CasinoBet'), 0) AS real_bet_usd,\n COALESCE(SUM(ABS(COALESCE(NULLIF(l.fiat_amount::text,'NaN')::numeric, 0)))\n FILTER (WHERE l.purpose = 'CasinoWin'), 0) AS real_win_usd,\n\n -- Bonus bets & wins (all flavours)\n COALESCE(SUM(ABS(COALESCE(NULLIF(l.fiat_amount::text,'NaN')::numeric, 0)))\n FILTER (WHERE l.purpose IN ('CasinoBetBonus','CasinoFreespinBet')), 0)\n AS bonus_bet_usd,\n COALESCE(SUM(ABS(COALESCE(NULLIF(l.fiat_amount::text,'NaN')::numeric, 0)))\n FILTER (WHERE l.purpose IN ('CasinoWinBonus','CasinoFreespinWin',\n 'CasinoFreeMoneyWin','CasinoBonusRoundWin')), 0)\n AS bonus_win_usd,\n\n -- Last REAL bet date (true churn signal — not bonus play)\n MAX(l.created_at) FILTER (WHERE l.purpose = 'CasinoBet') AS last_real_bet_at,\n\n -- Expected GGR on real bets only (RTP-adjusted)\n COALESCE(SUM(\n CASE\n WHEN cg.return_to_player IS NULL THEN 0\n ELSE\n ABS(COALESCE(NULLIF(l.fiat_amount::text,'NaN')::numeric, 0))\n * CASE\n WHEN cg.wagering_contribution IS NULL THEN 1.0\n WHEN cg.wagering_contribution > 1 THEN cg.wagering_contribution / 100.0\n ELSE cg.wagering_contribution\n END\n * (1 - CASE\n WHEN cg.return_to_player > 1 THEN cg.return_to_player / 100.0\n ELSE cg.return_to_player\n END)\n END\n ) FILTER (WHERE l.purpose = 'CasinoBet'), 0) AS expected_ggr_real_usd,\n\n -- Round count for activity signal\n COUNT(DISTINCT ct.round_id) FILTER (WHERE l.purpose = 'CasinoBet')\n AS real_round_count\n\n FROM public.casino_transactions ct\n JOIN public.ledgers l\n ON l.id = ct.ledger_id\n LEFT JOIN public.casino_games cg\n ON cg.id = ct.game_id\n WHERE ct.user_id IN (SELECT user_id FROM churn_candidates) -- ← scoped\n GROUP BY ct.user_id\n),\n\n-- ────────────────────────────────────────────────────────────\n-- 5. FAVOURITE PROVIDER\n-- Provider the player has wagered the most real money on\n-- (lifetime). Used for personalising the free spins offer.\n-- Scoped to churn_candidates — same small set.\n-- ────────────────────────────────────────────────────────────\nfavorite_provider AS (\n SELECT DISTINCT ON (user_id)\n user_id,\n provider_name,\n provider_real_bet_usd\n FROM (\n SELECT\n ct.user_id,\n COALESCE(\n cp.name->>'en',\n cp.name->>'EN',\n cp.unique_id\n ) AS provider_name,\n SUM(ABS(COALESCE(NULLIF(l.fiat_amount::text,'NaN')::numeric, 0)))\n AS provider_real_bet_usd\n FROM public.casino_transactions ct\n JOIN public.ledgers l\n ON l.id = ct.ledger_id\n AND l.purpose = 'CasinoBet'\n JOIN public.casino_games cg\n ON cg.id = ct.game_id\n JOIN public.casino_providers cp\n ON cp.id = cg.casino_provider_id\n WHERE ct.user_id IN (SELECT user_id FROM churn_candidates) -- ← scoped\n GROUP BY ct.user_id, cp.id, cp.name, cp.unique_id\n ) ranked\n ORDER BY user_id, provider_real_bet_usd DESC\n),\n\n-- ────────────────────────────────────────────────────────────\n-- 6. COMBINE + CALCULATE DERIVED METRICS\n-- ────────────────────────────────────────────────────────────\ncombined AS (\n SELECT\n dc.user_id,\n dc.username,\n dc.email,\n dc.deposit_count,\n ROUND(dc.total_deposit_usd::numeric, 2) AS total_deposit_usd,\n ROUND(dc.avg_deposit_usd::numeric, 2) AS avg_deposit_usd,\n ROUND(dc.deposit_usd_last_90d::numeric, 2) AS deposit_usd_last_90d,\n dc.first_deposit_at::date AS first_deposit_date,\n dc.last_deposit_at::date AS last_deposit_date,\n cs.last_real_bet_at::date AS last_real_bet_date,\n (CURRENT_DATE - dc.last_deposit_at::date) AS days_since_deposit,\n (CURRENT_DATE - cs.last_real_bet_at::date) AS days_since_real_bet,\n\n ROUND(cs.real_bet_usd::numeric, 2) AS real_bet_usd,\n ROUND(cs.bonus_bet_usd::numeric, 2) AS bonus_bet_usd,\n ROUND((cs.real_bet_usd - cs.real_win_usd)::numeric, 2) AS real_ggr_usd,\n cs.real_round_count,\n\n fp.provider_name AS favorite_provider,\n\n -- ── Key ratios ──────────────────────────────────────\n\n -- A) What % of their total wagering is real money?\n -- Low = mostly bonus bettor. Hard floor at 0.\n ROUND(\n CASE WHEN (cs.real_bet_usd + cs.bonus_bet_usd) > 0\n THEN cs.real_bet_usd / (cs.real_bet_usd + cs.bonus_bet_usd)\n ELSE 0\n END::numeric, 3\n ) AS real_bet_ratio,\n\n -- B) How deeply do they wager their deposits with real money?\n -- High = they actually play, not just deposit and sit\n ROUND(\n CASE WHEN dc.total_deposit_usd > 0\n THEN cs.real_bet_usd / dc.total_deposit_usd\n ELSE 0\n END::numeric, 2\n ) AS real_wager_depth,\n\n -- C) Effective house edge on real bets (RTP-adjusted)\n -- Low = plays mostly high-RTP games. Not a dealbreaker alone.\n -- NULL means no RTP data available in casino_games.\n ROUND(\n CASE WHEN cs.real_bet_usd > 0\n THEN cs.expected_ggr_real_usd / cs.real_bet_usd\n ELSE NULL\n END::numeric, 4\n ) AS effective_house_edge\n\n FROM deposit_stats dc\n LEFT JOIN casino_stats cs ON cs.user_id = dc.user_id\n LEFT JOIN favorite_provider fp ON fp.user_id = dc.user_id\n),\n\n-- ────────────────────────────────────────────────────────────\n-- 7. SCORING\n--\n-- Four components, weighted to reflect what matters most\n-- for a no-deposit free spins offer:\n--\n-- [40%] Real Bet Ratio\n-- Most important. We only make money on real bets.\n-- Full score at ≥70% real. Scales linearly below.\n--\n-- [30%] Real Wager Depth (real bets / total deposits)\n-- Did they actually play their deposits or just park\n-- them? Full score at ≥3× depth.\n--\n-- [20%] House Edge Quality (expected GGR / real bets)\n-- Penalises players who only grind high-RTP games.\n-- Full score at ≥3% effective edge.\n-- If no RTP data → neutral score (0.5).\n--\n-- [10%] Deposit Frequency\n-- More deposits = more committed player.\n-- Full score at ≥5 deposits.\n--\n-- Max score = 1.00\n-- ────────────────────────────────────────────────────────────\nscored AS (\n SELECT\n *,\n\n -- Component scores (each capped at their weighted max)\n LEAST(real_bet_ratio / 0.70, 1.0) * 0.40 AS score_real_ratio,\n LEAST(real_wager_depth / 3.0, 1.0) * 0.30 AS score_wager_depth,\n LEAST(COALESCE(effective_house_edge / 0.03, 0.5), 1.0) * 0.20\n AS score_house_edge,\n LEAST(deposit_count::float / 5.0, 1.0) * 0.10 AS score_deposit_freq\n\n FROM combined\n),\n\nfinal AS (\n SELECT\n *,\n\n ROUND((\n score_real_ratio +\n score_wager_depth +\n score_house_edge +\n score_deposit_freq\n )::numeric, 3) AS quality_score,\n\n -- Is the last real bet also in the 7–15d window?\n (days_since_real_bet BETWEEN 7 AND 15) AS bet_in_window\n\n FROM scored\n)\n\n-- ────────────────────────────────────────────────────────────\n-- 8. FINAL OUTPUT\n-- All candidates returned with a clear decision + reason.\n-- Sort: sends first (by score), then review, then exclude.\n-- ────────────────────────────────────────────────────────────\nSELECT\n -- Identity\n user_id,\n username,\n email,\n favorite_provider,\n\n -- Timing\n last_deposit_date,\n last_real_bet_date,\n days_since_deposit,\n days_since_real_bet,\n\n -- Activity\n deposit_count,\n first_deposit_date,\n avg_deposit_usd,\n total_deposit_usd,\n deposit_usd_last_90d,\n real_bet_usd,\n bonus_bet_usd,\n real_ggr_usd,\n real_round_count,\n\n -- Ratios\n ROUND(real_bet_ratio * 100, 1) AS real_bet_pct,\n real_wager_depth,\n ROUND(COALESCE(effective_house_edge, 0) * 100, 2) AS effective_house_edge_pct,\n\n -- Score breakdown\n ROUND(score_real_ratio::numeric, 3) AS score_real_ratio,\n ROUND(score_wager_depth::numeric, 3) AS score_wager_depth,\n ROUND(score_house_edge::numeric, 3) AS score_house_edge,\n ROUND(score_deposit_freq::numeric, 3) AS score_deposit_freq,\n quality2026-07-01T09:06:22.804427147Z _score,\n\n -- Decision\n CASE\n WHEN NOT bet_in_window THEN '❌ EXCLUDE'\n WHEN real_bet_ratio < 0.25 THEN '❌ EXCLUDE'\n -- Rule 1: ≤3 deposits = still onboarding, always keep (overrides rules 2 & 3)\n -- Rule 2: established player gone quiet\n WHEN deposit_count > 3\n AND deposit_usd_last_90d < 100 THEN '❌ EXCLUDE'\n -- Rule 3: micro-depositor with no meaningful recent volume\n -- (rule 1 carve-out still applies — deposit_count ≤3 never reaches here)\n WHEN deposit_count > 3\n AND avg_deposit_usd < 10\n AND deposit_usd_last_90d < 200 THEN '❌ EXCLUDE'\n WHEN quality_score >= 0.60 THEN '✅ SEND'\n WHEN quality_score >= 0.40 THEN '⚠️ REVIEW'\n ELSE '❌ EXCLUDE'\n END AS decision,\n\n -- Human-readable reason for every exclusion / review flag\n CASE\n WHEN NOT bet_in_window\n THEN 'Last real bet outside 7–15d window (deposited but may still be active, or never bet)'\n WHEN real_bet_ratio < 0.25\n THEN 'Mostly bonus bettor (' || ROUND(real_bet_ratio * 100, 1)::text || '% real) — free spins likely wasted'\n WHEN deposit_count > 3\n AND deposit_usd_last_90d < 100\n THEN 'Rule 2: established player gone quiet — only $' || deposit_usd_last_90d::text || ' deposited in last 90 days'\n WHEN deposit_count > 3\n AND avg_deposit_usd < 10\n AND deposit_usd_last_90d < 200\n THEN 'Rule 3: micro-depositor — avg $' || avg_deposit_usd::text || ' per deposit, only $' || deposit_usd_last_90d::text || ' in last 90 days'\n WHEN quality_score < 0.40\n THEN 'Low quality score (' || quality_score::text || ') — weak across multiple dimensions'\n WHEN quality_score < 0.60\n THEN 'Borderline score (' || quality_score::text || ') — manual review recommended'\n ELSE\n 'Qualifies on all criteria'\n END AS decision_reason\n\nFROM final\nORDER BY\n CASE\n WHEN NOT bet_in_window THEN 3\n WHEN real_bet_ratio < 0.25 THEN 3\n WHEN quality_score >= 0.60 THEN 1\n WHEN quality_score >= 0.40 THEN 2\n ELSE 3\n END,\n quality_score DESC;"}],
2026-Jul-01 09:06:22 :middleware
2026-Jul-01 09:06:22 {:js-int-to-string? true, :ignore-cached-results? false, :process-viz-settings? false, :userland-query? true},
2026-Jul-01 09:06:22 :cache-strategy
2026-Jul-01 09:06:22 {:multiplier 10,
2026-Jul-01 09:06:22 :min_duration_ms 1000,
2026-Jul-01 09:06:22 :type :ttl,
2026-Jul-01 09:06:22 :invalidated-at #t "2026-05-05T11:10:51.796809Z",
2026-Jul-01 09:06:22 :avg-execution-ms 92641},
2026-Jul-01 09:06:22 :viz-settings {:table.pivot_column "decision", :table.cell_column "days_since_deposit"},
2026-Jul-01 09:06:22 :database 2},
2026-Jul-01 09:06:22 :status :failed,
2026-Jul-01 09:06:22 :class java.net.SocketTimeoutException,
2026-Jul-01 09:06:22 :stacktrace
2026-Jul-01 09:06:22 ["java.base/sun.nio.ch.NioSocketImpl.timedFinishConnect(Unknown Source)"
2026-Jul-01 09:06:22 "java.base/sun.nio.ch.NioSocketImpl.connect(Unknown Source)"
2026-Jul-01 09:06:22 "java.base/java.net.SocksSocketImpl.connect(Unknown Source)"
2026-Jul-01 09:06:22 "java.base/java.net.Socket.connect(Unknown Source)"
2026-Jul-01 09:06:22 "org.postgresql.core.PGStream.createSocket(PGStream.java:261)"
2026-Jul-01 09:06:22 "org.postgresql.core.PGStream.(PGStream.java:122)"
2026-Jul-01 09:06:22 "org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:146)"
2026-Jul-01 09:06:22 "org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:289)"
2026-Jul-01 09:06:22 "org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:57)"
2026-Jul-01 09:06:22 "org.postgresql.jdbc.PgConnection.(PgConnection.java:279)"
2026-Jul-01 09:06:22 "org.postgresql.Driver.makeConnection(Driver.java:448)"
2026-Jul-01 09:06:22 "org.postgresql.Driver.connect(Driver.java:298)"
2026-Jul-01 09:06:22 "java.sql/java.sql.DriverManager.getConnection(Unknown Source)"
2026-Jul-01 09:06:22 "java.sql/java.sql.DriverManager.getConnection(Unknown Source)"
2026-Jul-01 09:06:22 "--> connection_pool$proxy_data_source$reify__42954.getConnection(connection_pool.clj:29)"],
2026-Jul-01 09:06:22 :card_id 547,
2026-Jul-01 09:06:22 :context :question,
2026-Jul-01 09:06:22 :error "The connection attempt failed.",
2026-Jul-01 09:06:22 :row_count 0,
2026-Jul-01 09:06:22 :running_time 0,
2026-Jul-01 09:06:22 :data {:rows [], :cols []}}
2026-Jul-01 09:06:22
2026-Jul-01 09:06:22 2026-07-01 09:06:22,806 DEBUG middleware.log :: POST /api/card/547/query 202 [ASYNC: completed] 10097ms (19 DB calls) App DB connections: 1/8 Jetty threads: 3/50 (4 idle, 0 queued) (117 total active threads) Queries in flight: 0 (0 queued); postgres DB 2 connections: 0/0 (0 threads blocked) {:metabase-user-id 15}
2026-Jul-01 09:06:23 2026-07-01 09:06:23,131 DEBUG middleware.log :: GET /api/database/2 200 5ms (2 DB calls) App DB connections: 1/8 Jetty threads: 3/50 (5 idle, 0 queued) (117 total active threads) Queries in flight: 0 (0 queued) {:metabase-user-id 15}