|
341 min
54%
|
3,655 ms
|
5,592
gateway-pg-user
|
SELECT CASE WHEN $3 < LENGTH(CAST("public"."onboarding_merchant_events"."metadata" AS TEXT)) THEN $4 ELSE "public"."onboarding_merchant_events"."metadata" END AS "metadata" FROM "public"."onboarding_merchant_events" INNER JOIN ((SELECT "public"."onboarding_merchant_events"."id" FROM "public"."onboarding_merchant_events" ORDER BY "public"."onboarding_merchant_events"."id" ASC LIMIT $1) UNION ALL (SELECT "public"."onboarding_merchant_events"."id" FROM "public"."onboarding_merchant_events" ORDER BY "public"."onboarding_merchant_events"."id" DESC LIMIT $2)) AS "result" ON ("result"."id" = "public"."onboarding_merchant_events"."id")
|
|
48 min
8%
|
542 ms
|
5,364
gateway-pg-user
|
-- Metabase:: userID: 1 queryType: native queryHash: 0bddb0d867fd99792662e3b98c871ca225cce987c5cdac7c206c8a8a562c32c2
with selected_date as (
select cast(cast( now()
as date) as timestamp) as ANALYSIS_DATE,
TO_CHAR(cast( now()
as date), $2) as ANALYSIS_DATE_VW
),
exec_date as (
select ANALYSIS_DATE, ANALYSIS_DATE_VW, ANALYSIS_DATE start_date, cast(ANALYSIS_DATE + INTERVAL $3 as timestamp) end_date
--cast(ANALYSIS_DATE - INTERVAL '2 days' as timestamp) start_date, cast((ANALYSIS_DATE - INTERVAL '1 day') + INTERVAL '23:59:59' as timestamp) end_date
from selected_date
),
merchants_with_transactions as (
select distinct ANALYSIS_DATE, ANALYSIS_DATE_VW, start_date, end_date,
tr.merchant_id,
tr.business_unit_id,
merchants.name AS merchant_name,
merchants.status AS merchant_status,
merchants.status_reason AS merchant_status_reason,
merchants.tax_number,
bu.name AS bu_name,
bu.branch,
bu.division,
bu.mcc,
bu.status AS bu_status,
bu.status_reason AS bu_status_reason,
bu.tax_number AS bu_tax_number
from exec_date ed
inner join transactions tr on tr.created_at between ed.start_date and ed.end_date
INNER JOIN merchants ON merchants.id = tr.merchant_id
LEFT JOIN merchant_business_units bu ON bu.merchant_id = merchants.id
AND bu.id = tr.business_unit_id
),
analysis_data as (
SELECT
start_date, end_date,
flt_merchants.ANALYSIS_DATE_VW,
flt_merchants.start_date,
flt_merchants.end_date,
flt_merchants.merchant_id,
flt_merchants.business_unit_id,
flt_merchants.merchant_name,
flt_merchants.merchant_status,
flt_merchants.merchant_status_reason,
flt_merchants.tax_number,
flt_merchants.bu_name,
flt_merchants.branch,
flt_merchants.division,
flt_merchants.mcc,
flt_merchants.bu_status,
flt_merchants.bu_status_reason,
flt_merchants.bu_tax_number,
-- Média e contagem das transações
COUNT(tr.amount) AS count_transaction_day,
sum(tr.amount) AS sum_amount_day,
MIN(tr.amount) AS min_amount_day,
MAX(tr.amount) AS max_amount_day,
AVG(tr.amount) AS avg_amount_day,
-- Desvio Padrão Transacional do dia
STDDEV_POP(tr.amount) AS stddev_transaction_day,
-- Contagem e média para os últimos 30, 60, 90 dias
-- 30 dias
(SELECT COUNT(*)
FROM transactions tr30
WHERE tr30.merchant_id = flt_merchants.merchant_id
AND tr30.business_unit_id = flt_merchants.business_unit_id
AND tr30.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $4) AND end_date
) AS count_30_days,
(SELECT AVG(tr30.amount)
FROM transactions tr30
WHERE tr30.merchant_id = flt_merchants.merchant_id
AND tr30.business_unit_id = flt_merchants.business_unit_id
AND tr30.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $5) AND end_date
) AS avg_30_days,
(SELECT STDDEV_POP(tr30.amount)
FROM transactions tr30
WHERE tr30.merchant_id = flt_merchants.merchant_id
AND tr30.business_unit_id = flt_merchants.business_unit_id
AND tr30.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $6) AND end_date
) AS stddev_30_days,
-- 60 dias
(SELECT COUNT(*)
FROM transactions tr60
WHERE tr60.merchant_id = flt_merchants.merchant_id
AND tr60.business_unit_id = flt_merchants.business_unit_id
AND tr60.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $7) AND end_date
) AS count_60_days,
(SELECT AVG(tr60.amount)
FROM transactions tr60
WHERE tr60.merchant_id = flt_merchants.merchant_id
AND tr60.business_unit_id = flt_merchants.business_unit_id
AND tr60.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $8) AND end_date
) AS avg_60_days,
(SELECT STDDEV_POP(tr60.amount)
FROM transactions tr60
WHERE tr60.merchant_id = flt_merchants.merchant_id
AND tr60.business_unit_id = flt_merchants.business_unit_id
AND tr60.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $9) AND end_date
) AS stddev_60_days,
-- 90 dias
(SELECT COUNT(*)
FROM transactions tr90
WHERE tr90.merchant_id = flt_merchants.merchant_id
AND tr90.business_unit_id = flt_merchants.business_unit_id
AND tr90.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $10) AND end_date
) AS count_90_days,
(SELECT AVG(tr90.amount)
FROM transactions tr90
WHERE tr90.merchant_id = flt_merchants.merchant_id
AND tr90.business_unit_id = flt_merchants.business_unit_id
AND tr90.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $11) AND end_date
) AS avg_90_days,
(SELECT STDDEV_POP(tr90.amount)
FROM transactions tr90
WHERE tr90.merchant_id = flt_merchants.merchant_id
AND tr90.business_unit_id = flt_merchants.business_unit_id
AND tr90.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $12) AND end_date
) AS stddev_90_days
FROM merchants_with_transactions flt_merchants
inner join transactions tr on tr.created_at between flt_merchants.start_date and flt_merchants.end_date
GROUP BY
start_date, end_date,
flt_merchants.ANALYSIS_DATE_VW,
flt_merchants.ANALYSIS_DATE,
flt_merchants.start_date,
flt_merchants.end_date,
flt_merchants.merchant_id,
flt_merchants.business_unit_id,
flt_merchants.merchant_name,
flt_merchants.merchant_status,
flt_merchants.merchant_status_reason,
flt_merchants.tax_number,
flt_merchants.bu_name,
flt_merchants.branch,
flt_merchants.division,
flt_merchants.mcc,
flt_merchants.bu_status,
flt_merchants.bu_status_reason,
flt_merchants.bu_tax_number
ORDER BY 1 DESC
),
score as (
select *,
-- Normalizando o Z-Score para o intervalo de 0 a 1 usando a fórmula de sigmoid
($13 / ($14 + EXP(-(avg_amount_day - avg_amount_day) / case when stddev_transaction_day <= $15 then $16 else stddev_transaction_day end))) AS weight_current_day,
(CASE
WHEN avg_30_days IS NOT NULL AND stddev_30_days > $17 THEN
$18 / ($19 + EXP(-(avg_amount_day - avg_30_days) / stddev_30_days))
ELSE $20
END) AS weight_30_days,
(CASE
WHEN avg_60_days IS NOT NULL AND stddev_60_days > $21 THEN
$22 / ($23 + EXP(-(avg_amount_day - avg_60_days) / stddev_60_days))
ELSE $24
END) AS weight_60_days,
(CASE
WHEN avg_90_days IS NOT NULL AND stddev_90_days > $25 THEN
$26 / ($27 + EXP(-(avg_amount_day - avg_90_days) / stddev_90_days))
ELSE $28
END) AS weight_90_days
from analysis_data
),
monitoring as (
select
*,
-- Flag de Anomalia: com base no peso normalizado
(CASE
WHEN weight_30_days >= $29 OR weight_60_days >= $30 OR weight_90_days >= $31 THEN $32 -- 'Alert'
WHEN weight_30_days >= $33 OR weight_60_days >= $34 OR weight_90_days >= $35 THEN $36 -- 'Monitoring'
ELSE weight_current_day -- 'Normal'
END) AS risk_score
from score
)
select * ,
-- Flag de Anomalia: com base no peso normalizado
(CASE
WHEN risk_score >= $37 THEN $38
WHEN risk_score >= $39 THEN $40
ELSE $41
END) AS anomaly_flag
from monitoring
where $42=$43
and risk_score >= (case $1-- FALSE
when $44 then $45
else $46 end )
|
|
9 min
1%
|
102 ms
|
5,188
gateway-pg-user
|
select coalesce((select json_agg(j.data) from (
select json_build_object($1::text, (json_build_object($2::text, sum($3))), $4::text, json_build_array((__local_0__."status")::text)) as data
from "public"."transactions" as __local_0__
where $5 = $6
group by __local_0__."status"
) j), $7::json) as "@groupedAggregates"
|
|
7 min
1%
|
99 ms
|
4,156
gateway-pg-user
|
SELECT "c"."column_name" AS "name", CASE WHEN "c"."udt_schema" IN ($3, $4) THEN FORMAT($5, "c"."udt_name") ELSE FORMAT($6, "c"."udt_schema", "c"."udt_name") END AS "database-type", "c"."ordinal_position" - $7 AS "database-position", "c"."table_schema" AS "table-schema", "c"."table_name" AS "table-name", "pk"."column_name" IS NOT NULL AS "pk?", COL_DESCRIPTION(CAST(CAST(FORMAT($8, CAST("c"."table_schema" AS TEXT), CAST("c"."table_name" AS TEXT)) AS REGCLASS) AS OID), "c"."ordinal_position") AS "field-comment", (("column_default" IS NULL) OR (LOWER("column_default") = $9)) AND ("is_nullable" = $10) AND NOT ((("column_default" IS NOT NULL) AND ("column_default" LIKE $11)) OR ("is_identity" <> $12)) AS "database-required", (("column_default" IS NOT NULL) AND ("column_default" LIKE $13)) OR ("is_identity" <> $14) AS "database-is-auto-increment" FROM "information_schema"."columns" AS "c" LEFT JOIN (SELECT "tc"."table_schema", "tc"."table_name", "kc"."column_name" FROM "information_schema"."table_constraints" AS "tc" INNER JOIN "information_schema"."key_column_usage" AS "kc" ON ("tc"."constraint_name" = "kc"."constraint_name") AND ("tc"."table_schema" = "kc"."table_schema") AND ("tc"."table_name" = "kc"."table_name") WHERE "tc"."constraint_type" = $15) AS "pk" ON ("c"."table_schema" = "pk"."table_schema") AND ("c"."table_name" = "pk"."table_name") AND ("c"."column_name" = "pk"."column_name") WHERE c.table_schema !~ $16 AND ("c"."table_schema" IN ($1)) UNION ALL SELECT "pa"."attname" AS "name", CASE WHEN "ptn"."nspname" IN ($17, $18) THEN FORMAT($19, "pt"."typname") ELSE FORMAT($20, "ptn"."nspname", "pt"."typname") END AS "database-type", "pa"."attnum" - $21 AS "database-position", "pn"."nspname" AS "table-schema", "pc"."relname" AS "table-name", $22 AS "pk?", $23 AS "field-comment", $24 AS "database-required", $25 AS "database-is-auto-increment" FROM "pg_catalog"."pg_class" AS "pc" INNER JOIN "pg_catalog"."pg_namespace" AS "pn" ON "pn"."oid" = "pc"."relnamespace" INNER JOIN "pg_catalog"."pg_attribute" AS "pa" ON "pa"."attrelid" = "pc"."oid" INNER JOIN "pg_catalog"."pg_type" AS "pt" ON "pt"."oid" = "pa"."atttypid" INNER JOIN "pg_catalog"."pg_namespace" AS "ptn" ON "ptn"."oid" = "pt"."typnamespace" WHERE ("pc"."relkind" = $26) AND ("pa"."attnum" >= $27) AND ("pn"."nspname" IN ($2)) ORDER BY "table-schema" ASC, "table-name" ASC, "database-position" ASC
|
|
6 min
1%
|
100 ms
|
3,768
gateway-pg-user
|
select to_json(json_build_array(((__local_0__."id")::numeric)::text)) as "__identifiers", to_json((with __local_1__ as (select to_json((json_build_object($3::text, json_build_array(((__local_2__."id")::numeric)::text), $4::text, ((__local_2__."amount"))::text, $5::text, (__local_2__."authorization_code"), $6::text, (__local_2__."blocked_payer_reason"), $7::text, (__local_2__."brand"), $8::text, ((__local_2__."business_unit_id"))::text, $9::text, (__local_2__."comments"), $10::text, (__local_2__."due_date"), $11::text, (__local_2__."external_id"), $12::text, (__local_2__."installment_interest_type"), $13::text, ((__local_2__."id"))::text, $14::text, (__local_2__."installment_number"), $15::text, (__local_2__."installment_total"), $16::text, (__local_2__."internal_id"), $17::text, ((__local_2__."merchant_id"))::text, $18::text, (__local_2__."movement_date"), $19::text, (__local_2__."nsu"), $20::text, (__local_2__."order_code"), $21::text, (__local_2__."payer_tax_number"), $22::text, ((__local_2__."payment_partner_id"))::text, $23::text, (__local_2__."refunded_at"), $24::text, (__local_2__."refund_status"), $25::text, (__local_2__."status"), $26::text, (__local_2__."status_reason"), $27::text, (__local_2__."tags"), $28::text, (__local_2__."transaction_info"), $29::text, ((__local_2__."transaction_parent_id"))::text, $30::text, (__local_2__."created_at"), $31::text, (__local_2__."deleted_at"), $32::text, (__local_2__."updated_at"), $33::text, (__local_2__."merchant_payment_identifier"), $34::text, (__local_2__."transaction_token"), $35::text, (__local_2__."currency_code"), $36::text, (select json_build_object($37::text, json_build_array(((__local_3__."id")::numeric)::text), $38::text, (__local_3__."payment_method_name")) as object
from "public"."payment_methods" as __local_3__
where (__local_2__."payment_method_id" = __local_3__."id") and ($39) and ($40)
), $41::text, (__local_2__."capture_method"), $42::text, (select json_build_object($43::text, json_build_array(((__local_4__."id")::numeric)::text), $44::text, (__local_4__."merchant_token"), $45::text, (__local_4__."name")) as object
from "public"."merchants" as __local_4__
where (__local_2__."merchant_id" = __local_4__."id") and ($46) and ($47)
), $48::text, (select json_build_object($49::text, json_build_array(((__local_5__."id")::numeric)::text), $50::text, (__local_5__."business_unit_token")) as object
from "public"."merchant_business_units" as __local_5__
where (__local_2__."business_unit_id" = __local_5__."id") and ($51) and ($52)
), $53::text, (__local_2__."invoice_token"), $54::text, (__local_2__."metadata")))) as "@nodes" from (select __local_2__.*
from "public"."transactions" as __local_2__
where (__local_2__."merchant_id" = __local_0__."id") and (__local_2__."business_unit_id" = $1) and (((__local_2__."id" NOT IN (select $55::"pg_catalog"."int8" limit $56)))) and ($57) and ($58)
order by __local_2__."created_at" DESC,__local_2__."id" ASC
limit $59
) __local_2__), __local_6__ as (select json_agg(to_json(__local_1__)) as data from __local_1__) select json_build_object($60::text, coalesce((select __local_6__.data from __local_6__), $61::json), $62::text, (
select json_build_object($63::text, count($64))
from "public"."transactions" as __local_2__
where (__local_2__."merchant_id" = __local_0__."id") and (__local_2__."business_unit_id" = $1) and (((__local_2__."id" NOT IN (select $65::"pg_catalog"."int8" limit $66))))
)) )) as "@allTransactions"
from "public"."merchants" as __local_0__
where (__local_0__."merchant_token" = $2) and ($67) and ($68)
|
|
4 min
0.6%
|
379 ms
|
628
gateway-pg-user
|
select to_json(json_build_array(((__local_0__."id")::numeric)::text)) as "__identifiers", to_json(((__local_0__."id"))::text) as "id", to_json((__local_0__."description")) as "description", to_json((__local_0__."capture_method")) as "captureMethod", to_json(((__local_0__."business_unit_id"))::text) as "businessUnitId", to_json(((__local_0__."amount"))::text) as "amount", to_json((__local_0__."due_date")) as "dueDate", to_json((__local_0__."expiration_date")) as "expirationDate", to_json((__local_0__."invoice_info")) as "invoiceInfo", to_json((__local_0__."installment_total")) as "installmentTotal", to_json((__local_0__."payer_tax_number")) as "payerTaxNumber", to_json((__local_0__."payer_name")) as "payerName", to_json((__local_0__."merchant_payment_id")) as "merchantPaymentId", to_json((select json_build_object($2::text, json_build_array(((__local_1__."id")::numeric)::text), $3::text, (__local_1__."business_unit_token")) as object
from "public"."merchant_business_units" as __local_1__
where (__local_0__."business_unit_id" = __local_1__."id") and ($4) and ($5)
)) as "@merchantBusinessUnitByBusinessUnitId", to_json((select json_build_object($6::text, json_build_array(((__local_2__."id")::numeric)::text), $7::text, (__local_2__."name"), $8::text, (__local_2__."merchant_token"), $9::text, (with __local_3__ as (select to_json((json_build_object($10::text, json_build_array(((__local_4__."id")::numeric)::text), $11::text, (__local_4__."status"), $12::text, (__local_4__."transaction_token"), $13::text, (__local_4__."updated_at"), $14::text, (select json_build_object($15::text, json_build_array(((__local_5__."id")::numeric)::text), $16::text, (__local_5__."payment_method_name")) as object
from "public"."payment_methods" as __local_5__
where (__local_4__."payment_method_id" = __local_5__."id") and ($17) and ($18)
)))) as "@nodes" from (select __local_4__.*
from "public"."transactions" as __local_4__
where (__local_4__."merchant_id" = __local_2__."id") and ($19) and ($20)
order by __local_4__."id" ASC
) __local_4__), __local_6__ as (select json_agg(to_json(__local_3__)) as data from __local_3__) select json_build_object($21::text, coalesce((select __local_6__.data from __local_6__), $22::json)) )) as object
from "public"."merchants" as __local_2__
where (__local_0__."merchant_id" = __local_2__."id") and ($23) and ($24)
)) as "@merchantByMerchantId"
from "public"."invoices" as __local_0__
where (__local_0__."invoice_token" = $1) and ($25) and ($26)
|
|
3 min
0.5%
|
246 ms
|
790
gateway-pg-user
|
select to_json(json_build_array(((__local_0__."id")::numeric)::text)) as "__identifiers", to_json(( select json_build_object($8::text, coalesce((select json_agg(j.data) from (
select json_build_object($9::text, (json_build_object($10::text, sum($11), $12::text, (json_build_object($13::text, coalesce(sum(__local_1__."amount"), $14))))), $15::text, json_build_array((__local_1__."status")::text, (date_trunc($16, __local_1__."created_at"))::text)) as data
from "public"."transactions" as __local_1__
where (__local_1__."merchant_id" = __local_0__."id") and (__local_1__."business_unit_id" = $1) and (((__local_1__."status" IN ($2,$3,$4))) and ((__local_1__."created_at" <= $5) and (__local_1__."created_at" >= $6)))
group by __local_1__."status", date_trunc($17, __local_1__."created_at")
) j), $18::json)) )) as "@paymentCompare", to_json(( select json_build_object($19::text, coalesce((select json_agg(j.data) from (
select json_build_object($20::text, (json_build_object($21::text, sum($22))), $23::text, json_build_array((__local_2__."status")::text)) as data
from "public"."transactions" as __local_2__
where (__local_2__."merchant_id" = __local_0__."id")
group by __local_2__."status"
) j), $24::json)) )) as "@StatusTransactionsKeys"
from "public"."merchants" as __local_0__
where (__local_0__."merchant_token" = $7) and ($25) and ($26)
|
|
2 min
0.4%
|
100 ms
|
1,436
gateway-pg-user
|
SELECT "c"."column_name" AS "name", CASE WHEN "c"."udt_schema" IN ($4, $5) THEN FORMAT($6, "c"."udt_name") ELSE FORMAT($7, "c"."udt_schema", "c"."udt_name") END AS "database-type", "c"."ordinal_position" - $8 AS "database-position", "c"."table_schema" AS "table-schema", "c"."table_name" AS "table-name", "pk"."column_name" IS NOT NULL AS "pk?", COL_DESCRIPTION(CAST(CAST(FORMAT($9, CAST("c"."table_schema" AS TEXT), CAST("c"."table_name" AS TEXT)) AS REGCLASS) AS OID), "c"."ordinal_position") AS "field-comment", (("column_default" IS NULL) OR (LOWER("column_default") = $10)) AND ("is_nullable" = $11) AND NOT ((("column_default" IS NOT NULL) AND ("column_default" LIKE $12)) OR ("is_identity" <> $13)) AS "database-required", "column_default" AS "database-default", (("column_default" IS NOT NULL) AND ("column_default" LIKE $14)) OR ("is_identity" <> $15) AS "database-is-auto-increment", "is_generated" = $1 AS "database-is-generated", "is_nullable" = $16 AS "database-is-nullable" FROM "information_schema"."columns" AS "c" LEFT JOIN (SELECT "tc"."table_schema", "tc"."table_name", "kc"."column_name" FROM "information_schema"."table_constraints" AS "tc" INNER JOIN "information_schema"."key_column_usage" AS "kc" ON ("tc"."constraint_name" = "kc"."constraint_name") AND ("tc"."table_schema" = "kc"."table_schema") AND ("tc"."table_name" = "kc"."table_name") WHERE "tc"."constraint_type" = $17) AS "pk" ON ("c"."table_schema" = "pk"."table_schema") AND ("c"."table_name" = "pk"."table_name") AND ("c"."column_name" = "pk"."column_name") WHERE c.table_schema !~ $18 AND ("c"."table_schema" IN ($2)) UNION ALL SELECT "pa"."attname" AS "name", CASE WHEN "ptn"."nspname" IN ($19, $20) THEN FORMAT($21, "pt"."typname") ELSE FORMAT($22, "ptn"."nspname", "pt"."typname") END AS "database-type", "pa"."attnum" - $23 AS "database-position", "pn"."nspname" AS "table-schema", "pc"."relname" AS "table-name", $24 AS "pk?", $25 AS "field-comment", $26 AS "database-required", $27 AS "database-default", $28 AS "database-is-auto-increment", $29 AS "database-is-generated", $30 AS "database-is-nullable" FROM "pg_catalog"."pg_class" AS "pc" INNER JOIN "pg_catalog"."pg_namespace" AS "pn" ON "pn"."oid" = "pc"."relnamespace" INNER JOIN "pg_catalog"."pg_attribute" AS "pa" ON "pa"."attrelid" = "pc"."oid" INNER JOIN "pg_catalog"."pg_type" AS "pt" ON "pt"."oid" = "pa"."atttypid" INNER JOIN "pg_catalog"."pg_namespace" AS "ptn" ON "ptn"."oid" = "pt"."typnamespace" WHERE ("pc"."relkind" = $31) AND ("pa"."attnum" >= $32) AND ("pn"."nspname" IN ($3)) ORDER BY "table-schema" ASC, "table-name" ASC, "database-position" ASC
|
|
2 min
0.4%
|
1,072 ms
|
134
gateway-pg-user
|
select te1_0.id,te1_0.amount,te1_0.authorization_code,te1_0.blocked_payer_reason,te1_0.brand,te1_0.business_unit_id,te1_0.canceled_reason,te1_0.capture_method,te1_0.card_brand,te1_0.card_expiration_month,te1_0.card_expiration_year,te1_0.card_pan,te1_0.comments,te1_0.created_at,te1_0.currency_code,te1_0.deleted_at,te1_0.due_date,te1_0.external_id,te1_0.fee_total_amount,te1_0.has_payer,te1_0.installment_interest_type,te1_0.installment_number,te1_0.installment_total,te1_0.internal_id,te1_0.invoice_token,te1_0.merchant_id,te1_0.merchant_payment_id,te1_0.merchant_payment_identifier,te1_0.metadata,te1_0.movement_date,te1_0.nsu,te1_0.paid_amount,te1_0.partner_tracking_id,te1_0.partner_transaction_id,te1_0.payer_tax_number,te1_0.payment_method_id,te1_0.payment_partner_id,te1_0.refund_status,te1_0.refunded_amount,te1_0.refunded_at,te1_0.session_key,te1_0.status,te1_0.status_reason,te1_0.tags,te1_0.transaction_info,te1_0.transaction_token,te1_0.transaction_type,te1_0.updated_at from transactions te1_0 where te1_0.partner_tracking_id=$1
Details
CREATE INDEX CONCURRENTLY ON transactions (partner_tracking_id)
Rows: 22438
Row progression: 22438, 1
Row estimates
- partner_tracking_id (=): 1
Existing indexes
- id PRIMARY
- merchant_id, business_unit_id, created_at
- plan_billing_token
- transaction_token UNIQUE
|
|
2 min
0.3%
|
231 ms
|
524
gateway-pg-user
|
select to_json(json_build_array(((__local_0__."id")::numeric)::text)) as "__identifiers", to_json(( select json_build_object($7::text, (
select json_build_object($8::text, (json_build_object($9::text, sum($10), $11::text, (json_build_object($12::text, coalesce(sum(__local_1__."amount"), $13))), $14::text, (json_build_object($15::text, avg(__local_1__."amount"))), $16::text, (json_build_object($17::text, count(distinct __local_1__."id"))))))
from "public"."transactions" as __local_1__
where (__local_1__."merchant_id" = __local_0__."id") and (__local_1__."business_unit_id" = $1) and (((__local_1__."status" NOT IN ($2,$3,$4,$5))))
)) )) as "@totalRevenue"
from "public"."merchants" as __local_0__
where (__local_0__."merchant_token" = $6) and ($18) and ($19)
|
|
2 min
0.3%
|
85 ms
|
1,397
gateway-pg-user
|
with __local_0__ as (select to_json((json_build_object($2::text, json_build_array(((__local_1__."id")::numeric)::text), $3::text, ((__local_1__."id"))::text, $4::text, ((__local_1__."amount"))::text, $5::text, (__local_1__."status"), $6::text, (__local_1__."transaction_token"), $7::text, ((__local_1__."merchant_id"))::text, $8::text, ((__local_1__."business_unit_id"))::text, $9::text, (__local_1__."created_at"), $10::text, (__local_1__."updated_at"), $11::text, (__local_1__."payer_tax_number"), $12::text, (__local_1__."metadata"), $13::text, (__local_1__."due_date"), $14::text, (__local_1__."brand"), $15::text, (__local_1__."nsu"), $16::text, (__local_1__."authorization_code"), $17::text, (__local_1__."capture_method"), $18::text, (__local_1__."currency_code"), $19::text, (__local_1__."invoice_token"), $20::text, (__local_1__."installment_total"), $21::text, (select json_build_object($22::text, json_build_array(((__local_2__."id")::numeric)::text), $23::text, (__local_2__."payment_method_name")) as object
from "public"."payment_methods" as __local_2__
where (__local_1__."payment_method_id" = __local_2__."id") and ($24) and ($25)
), $26::text, (select json_build_object($27::text, json_build_array(((__local_3__."id")::numeric)::text), $28::text, (__local_3__."merchant_token")) as object
from "public"."merchants" as __local_3__
where (__local_1__."merchant_id" = __local_3__."id") and ($29) and ($30)
), $31::text, (select json_build_object($32::text, json_build_array(((__local_4__."id")::numeric)::text), $33::text, (__local_4__."business_unit_token")) as object
from "public"."merchant_business_units" as __local_4__
where (__local_1__."business_unit_id" = __local_4__."id") and ($34) and ($35)
), $36::text, (__local_1__."merchant_payment_identifier")))) as "@nodes" from (select __local_1__.*
from "public"."transactions" as __local_1__
where (__local_1__."invoice_token" = $1) and ($37) and ($38)
order by __local_1__."id" ASC
) __local_1__), __local_5__ as (select json_agg(to_json(__local_0__)) as data from __local_0__) select coalesce((select __local_5__.data from __local_5__), $39::json) as "data"
|
|
2 min
0.3%
|
78 ms
|
1,266
gateway-pg-user
|
with __local_0__ as (select to_json((json_build_object($2::text, json_build_array(((__local_1__."id")::numeric)::text), $3::text, ((__local_1__."id"))::text, $4::text, ((__local_1__."amount"))::text, $5::text, (__local_1__."status"), $6::text, (__local_1__."transaction_token"), $7::text, ((__local_1__."merchant_id"))::text, $8::text, ((__local_1__."business_unit_id"))::text, $9::text, (__local_1__."created_at"), $10::text, (__local_1__."updated_at"), $11::text, (__local_1__."payer_tax_number"), $12::text, (__local_1__."metadata"), $13::text, (__local_1__."due_date"), $14::text, (__local_1__."brand"), $15::text, (__local_1__."nsu"), $16::text, (__local_1__."authorization_code"), $17::text, (__local_1__."capture_method"), $18::text, (__local_1__."currency_code"), $19::text, (__local_1__."invoice_token"), $20::text, (__local_1__."installment_total"), $21::text, (select json_build_object($22::text, json_build_array(((__local_2__."id")::numeric)::text), $23::text, (__local_2__."payment_method_name")) as object
from "public"."payment_methods" as __local_2__
where (__local_1__."payment_method_id" = __local_2__."id") and ($24) and ($25)
), $26::text, (select json_build_object($27::text, json_build_array(((__local_3__."id")::numeric)::text), $28::text, (__local_3__."merchant_token")) as object
from "public"."merchants" as __local_3__
where (__local_1__."merchant_id" = __local_3__."id") and ($29) and ($30)
), $31::text, (select json_build_object($32::text, json_build_array(((__local_4__."id")::numeric)::text), $33::text, (__local_4__."business_unit_token"), $34::text, (select json_build_object($35::text, json_build_array(((__local_5__."id")::numeric)::text), $36::text, (__local_5__."name")) as object
from "public"."merchants" as __local_5__
where (__local_4__."merchant_id" = __local_5__."id") and ($37) and ($38)
)) as object
from "public"."merchant_business_units" as __local_4__
where (__local_1__."business_unit_id" = __local_4__."id") and ($39) and ($40)
), $41::text, (__local_1__."merchant_payment_identifier")))) as "@nodes" from (select __local_1__.*
from "public"."transactions" as __local_1__
where (__local_1__."invoice_token" = $1) and ($42) and ($43)
order by __local_1__."id" ASC
) __local_1__), __local_6__ as (select json_agg(to_json(__local_0__)) as data from __local_0__) select coalesce((select __local_6__.data from __local_6__), $44::json) as "data"
|
|
1 min
0.1%
|
78 ms
|
694
gateway-pg-user
|
select to_json(json_build_array(((__local_0__."id")::numeric)::text)) as "__identifiers", to_json((with __local_1__ as (select to_json((json_build_object($5::text, json_build_array(((__local_2__."id")::numeric)::text), $6::text, (__local_2__."metadata"), $7::text, (__local_2__."order_code")))) as "@list" from (select __local_2__.*
from "public"."transactions" as __local_2__
where (__local_2__."merchant_id" = __local_0__."id") and (__local_2__."business_unit_id" = $1) and (((__local_2__."created_at" <= $2) and (__local_2__."created_at" >= $3))) and ($8) and ($9)
order by __local_2__."created_at" DESC,__local_2__."id" ASC
) __local_2__), __local_3__ as (select json_agg(to_json(__local_1__)) as data from __local_1__) select json_build_object($10::text, coalesce((select __local_3__.data from __local_3__), $11::json)) )) as "@payers"
from "public"."merchants" as __local_0__
where (__local_0__."merchant_token" = $4) and ($12) and ($13)
|
|
1 min
0.1%
|
168 ms
|
263
gateway-pg-user
|
-- Metabase:: userID: 1 queryType: native queryHash: 25009ce1f2f3e41c03fe8b5a4979e2a976a0bfe1e3dbcc1d7450871e4b3e7a46
with selected_date as (
select cast(cast( now()
as date) as timestamp) as ANALYSIS_DATE,
TO_CHAR(cast( now()
as date), $1) as ANALYSIS_DATE_VW
),
exec_date as (
select ANALYSIS_DATE, ANALYSIS_DATE_VW, ANALYSIS_DATE start_date, cast(ANALYSIS_DATE + INTERVAL $2 as timestamp) end_date
--cast(ANALYSIS_DATE - INTERVAL '2 days' as timestamp) start_date, cast((ANALYSIS_DATE - INTERVAL '1 day') + INTERVAL '23:59:59' as timestamp) end_date
from selected_date
),
merchants_with_transactions as (
select distinct ANALYSIS_DATE, ANALYSIS_DATE_VW, start_date, end_date,
tr.merchant_id,
tr.business_unit_id,
merchants.name AS merchant_name,
merchants.status AS merchant_status,
merchants.status_reason AS merchant_status_reason,
merchants.tax_number,
bu.name AS bu_name,
bu.branch,
bu.division,
bu.mcc,
bu.status AS bu_status,
bu.status_reason AS bu_status_reason,
bu.tax_number AS bu_tax_number
from exec_date ed
inner join transactions tr on tr.created_at between ed.start_date and ed.end_date
INNER JOIN merchants ON merchants.id = tr.merchant_id
LEFT JOIN merchant_business_units bu ON bu.merchant_id = merchants.id
AND bu.id = tr.business_unit_id
),
analysis_data as (
SELECT
start_date, end_date,
flt_merchants.ANALYSIS_DATE_VW,
flt_merchants.start_date,
flt_merchants.end_date,
flt_merchants.merchant_id,
flt_merchants.business_unit_id,
flt_merchants.merchant_name,
flt_merchants.merchant_status,
flt_merchants.merchant_status_reason,
flt_merchants.tax_number,
flt_merchants.bu_name,
flt_merchants.branch,
flt_merchants.division,
flt_merchants.mcc,
flt_merchants.bu_status,
flt_merchants.bu_status_reason,
flt_merchants.bu_tax_number,
-- Média e contagem das transações
COUNT(tr.amount) AS count_transaction_day,
sum(tr.amount) AS sum_amount_day,
MIN(tr.amount) AS min_amount_day,
MAX(tr.amount) AS max_amount_day,
AVG(tr.amount) AS avg_amount_day,
-- Desvio Padrão Transacional do dia
STDDEV_POP(tr.amount) AS stddev_transaction_day,
-- Contagem e média para os últimos 30, 60, 90 dias
-- 30 dias
(SELECT COUNT(*)
FROM transactions tr30
WHERE tr30.merchant_id = flt_merchants.merchant_id
AND tr30.business_unit_id = flt_merchants.business_unit_id
AND tr30.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $3) AND end_date
) AS count_30_days,
(SELECT AVG(tr30.amount)
FROM transactions tr30
WHERE tr30.merchant_id = flt_merchants.merchant_id
AND tr30.business_unit_id = flt_merchants.business_unit_id
AND tr30.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $4) AND end_date
) AS avg_30_days,
(SELECT STDDEV_POP(tr30.amount)
FROM transactions tr30
WHERE tr30.merchant_id = flt_merchants.merchant_id
AND tr30.business_unit_id = flt_merchants.business_unit_id
AND tr30.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $5) AND end_date
) AS stddev_30_days,
-- 60 dias
(SELECT COUNT(*)
FROM transactions tr60
WHERE tr60.merchant_id = flt_merchants.merchant_id
AND tr60.business_unit_id = flt_merchants.business_unit_id
AND tr60.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $6) AND end_date
) AS count_60_days,
(SELECT AVG(tr60.amount)
FROM transactions tr60
WHERE tr60.merchant_id = flt_merchants.merchant_id
AND tr60.business_unit_id = flt_merchants.business_unit_id
AND tr60.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $7) AND end_date
) AS avg_60_days,
(SELECT STDDEV_POP(tr60.amount)
FROM transactions tr60
WHERE tr60.merchant_id = flt_merchants.merchant_id
AND tr60.business_unit_id = flt_merchants.business_unit_id
AND tr60.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $8) AND end_date
) AS stddev_60_days,
-- 90 dias
(SELECT COUNT(*)
FROM transactions tr90
WHERE tr90.merchant_id = flt_merchants.merchant_id
AND tr90.business_unit_id = flt_merchants.business_unit_id
AND tr90.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $9) AND end_date
) AS count_90_days,
(SELECT AVG(tr90.amount)
FROM transactions tr90
WHERE tr90.merchant_id = flt_merchants.merchant_id
AND tr90.business_unit_id = flt_merchants.business_unit_id
AND tr90.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $10) AND end_date
) AS avg_90_days,
(SELECT STDDEV_POP(tr90.amount)
FROM transactions tr90
WHERE tr90.merchant_id = flt_merchants.merchant_id
AND tr90.business_unit_id = flt_merchants.business_unit_id
AND tr90.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $11) AND end_date
) AS stddev_90_days
FROM merchants_with_transactions flt_merchants
inner join transactions tr on tr.created_at between flt_merchants.start_date and flt_merchants.end_date
GROUP BY
start_date, end_date,
flt_merchants.ANALYSIS_DATE_VW,
flt_merchants.ANALYSIS_DATE,
flt_merchants.start_date,
flt_merchants.end_date,
flt_merchants.merchant_id,
flt_merchants.business_unit_id,
flt_merchants.merchant_name,
flt_merchants.merchant_status,
flt_merchants.merchant_status_reason,
flt_merchants.tax_number,
flt_merchants.bu_name,
flt_merchants.branch,
flt_merchants.division,
flt_merchants.mcc,
flt_merchants.bu_status,
flt_merchants.bu_status_reason,
flt_merchants.bu_tax_number
ORDER BY 1 DESC
),
score as (
select *,
-- Normalizando o Z-Score para o intervalo de 0 a 1 usando a fórmula de sigmoid
($12 / ($13 + EXP(-(avg_amount_day - avg_amount_day) / case when stddev_transaction_day <= $14 then $15 else stddev_transaction_day end))) AS weight_current_day,
(CASE
WHEN avg_30_days IS NOT NULL AND stddev_30_days > $16 THEN
$17 / ($18 + EXP(-(avg_amount_day - avg_30_days) / stddev_30_days))
ELSE $19
END) AS weight_30_days,
(CASE
WHEN avg_60_days IS NOT NULL AND stddev_60_days > $20 THEN
$21 / ($22 + EXP(-(avg_amount_day - avg_60_days) / stddev_60_days))
ELSE $23
END) AS weight_60_days,
(CASE
WHEN avg_90_days IS NOT NULL AND stddev_90_days > $24 THEN
$25 / ($26 + EXP(-(avg_amount_day - avg_90_days) / stddev_90_days))
ELSE $27
END) AS weight_90_days
from analysis_data
),
monitoring as (
select
*,
-- Flag de Anomalia: com base no peso normalizado
(CASE
WHEN weight_30_days >= $28 OR weight_60_days >= $29 OR weight_90_days >= $30 THEN $31 -- 'Alert'
WHEN weight_30_days >= $32 OR weight_60_days >= $33 OR weight_90_days >= $34 THEN $35 -- 'Monitoring'
ELSE weight_current_day -- 'Normal'
END) AS risk_score
from score
)
select * ,
-- Flag de Anomalia: com base no peso normalizado
(CASE
WHEN risk_score >= $36 THEN $37
WHEN risk_score >= $38 THEN $39
ELSE $40
END) AS anomaly_flag
from monitoring
|
|
0 min
< 0.1%
|
46 ms
|
620
gateway-pg-user
|
WITH query_stats AS ( SELECT LEFT(query, $1) AS query, queryid AS query_hash, rolname AS user, ((total_plan_time + total_exec_time) / $2 / $3) AS total_minutes, ((total_plan_time + total_exec_time) / calls) AS average_time, calls FROM pg_stat_statements INNER JOIN pg_database ON pg_database.oid = pg_stat_statements.dbid INNER JOIN pg_roles ON pg_roles.oid = pg_stat_statements.userid WHERE calls > $4 AND pg_database.datname = current_database() ) SELECT query, query AS explainable_query, query_hash, query_stats.user, total_minutes, average_time, calls, total_minutes * $5 / (SELECT SUM(total_minutes) FROM query_stats) AS total_percent, (SELECT SUM(total_minutes) FROM query_stats) AS all_queries_total_minutes FROM query_stats ORDER BY "total_minutes" DESC LIMIT $6 /*pghero*/
|
|
0 min
< 0.1%
|
182 ms
|
133
gateway-pg-user
|
select to_json(json_build_array(((__local_0__."id")::numeric)::text)) as "__identifiers", to_json(( select json_build_object($3::text, (
select json_build_object($4::text, (json_build_object($5::text, sum($6), $7::text, (json_build_object($8::text, coalesce(sum(__local_1__."amount"), $9))), $10::text, (json_build_object($11::text, avg(__local_1__."amount"))), $12::text, (json_build_object($13::text, count(distinct __local_1__."id"))))))
from "public"."transactions" as __local_1__
where (__local_1__."merchant_id" = __local_0__."id") and (__local_1__."business_unit_id" = $1)
)) )) as "@totalRevenue"
from "public"."merchants" as __local_0__
where (__local_0__."merchant_token" = $2) and ($14) and ($15)
|
|
0 min
< 0.1%
|
32 ms
|
641
gateway-pg-user
|
SELECT schemaname AS schema, t.relname AS table, ix.relname AS name, regexp_replace(pg_get_indexdef(i.indexrelid), $1, $2) AS columns, regexp_replace(pg_get_indexdef(i.indexrelid), $3, $4) AS using, indisunique AS unique, indisprimary AS primary, indisvalid AS valid, indexprs::text, indpred::text, pg_get_indexdef(i.indexrelid) AS definition FROM pg_index i INNER JOIN pg_class t ON t.oid = i.indrelid INNER JOIN pg_class ix ON ix.oid = i.indexrelid LEFT JOIN pg_stat_user_indexes ui ON ui.indexrelid = i.indexrelid WHERE schemaname IS NOT NULL ORDER BY 1, 2 /*pghero*/
|
|
0 min
< 0.1%
|
31 ms
|
595
gateway-pg-user
|
select to_json(json_build_array(((__local_0__."id")::numeric)::text)) as "__identifiers", to_json((with __local_1__ as (select to_json((json_build_object($3::text, json_build_array(((__local_2__."id")::numeric)::text), $4::text, ((__local_2__."amount"))::text, $5::text, (__local_2__."authorization_code"), $6::text, (__local_2__."blocked_payer_reason"), $7::text, (__local_2__."brand"), $8::text, ((__local_2__."business_unit_id"))::text, $9::text, (__local_2__."comments"), $10::text, (__local_2__."due_date"), $11::text, (__local_2__."external_id"), $12::text, (__local_2__."installment_interest_type"), $13::text, ((__local_2__."id"))::text, $14::text, (__local_2__."installment_number"), $15::text, (__local_2__."installment_total"), $16::text, (__local_2__."internal_id"), $17::text, ((__local_2__."merchant_id"))::text, $18::text, (__local_2__."movement_date"), $19::text, (__local_2__."nsu"), $20::text, (__local_2__."order_code"), $21::text, (__local_2__."payer_tax_number"), $22::text, ((__local_2__."payment_partner_id"))::text, $23::text, (__local_2__."refunded_at"), $24::text, (__local_2__."refund_status"), $25::text, (__local_2__."status"), $26::text, (__local_2__."status_reason"), $27::text, (__local_2__."tags"), $28::text, (__local_2__."transaction_info"), $29::text, ((__local_2__."transaction_parent_id"))::text, $30::text, (__local_2__."created_at"), $31::text, (__local_2__."deleted_at"), $32::text, (__local_2__."updated_at"), $33::text, (__local_2__."merchant_payment_identifier"), $34::text, (__local_2__."transaction_token"), $35::text, (__local_2__."currency_code"), $36::text, (select json_build_object($37::text, json_build_array(((__local_3__."id")::numeric)::text), $38::text, (__local_3__."payment_method_name")) as object
from "public"."payment_methods" as __local_3__
where (__local_2__."payment_method_id" = __local_3__."id") and ($39) and ($40)
), $41::text, (__local_2__."capture_method"), $42::text, (select json_build_object($43::text, json_build_array(((__local_4__."id")::numeric)::text), $44::text, (__local_4__."merchant_token"), $45::text, (__local_4__."name")) as object
from "public"."merchants" as __local_4__
where (__local_2__."merchant_id" = __local_4__."id") and ($46) and ($47)
), $48::text, (select json_build_object($49::text, json_build_array(((__local_5__."id")::numeric)::text), $50::text, (__local_5__."business_unit_token")) as object
from "public"."merchant_business_units" as __local_5__
where (__local_2__."business_unit_id" = __local_5__."id") and ($51) and ($52)
), $53::text, (__local_2__."invoice_token"), $54::text, (__local_2__."metadata")))) as "@nodes" from (select __local_2__.*
from "public"."transactions" as __local_2__
where (__local_2__."merchant_id" = __local_0__."id") and (__local_2__."business_unit_id" = $1) and (((__local_2__."id" NOT IN (select $55::"pg_catalog"."int8" limit $56)))) and ($57) and ($58)
order by __local_2__."created_at" DESC,__local_2__."id" ASC
limit $59
offset $60) __local_2__), __local_6__ as (select json_agg(to_json(__local_1__)) as data from __local_1__) select json_build_object($61::text, coalesce((select __local_6__.data from __local_6__), $62::json), $63::text, (
select json_build_object($64::text, count($65))
from "public"."transactions" as __local_2__
where (__local_2__."merchant_id" = __local_0__."id") and (__local_2__."business_unit_id" = $1) and (((__local_2__."id" NOT IN (select $66::"pg_catalog"."int8" limit $67))))
)) )) as "@allTransactions"
from "public"."merchants" as __local_0__
where (__local_0__."merchant_token" = $2) and ($68) and ($69)
|
|
0 min
< 0.1%
|
57 ms
|
284
gateway-pg-user
|
SELECT t.oid,t.*,c.relkind,format_type(nullif(t.typbasetype, $1), t.typtypmod) as base_type_name, d.description
FROM pg_catalog.pg_type t
LEFT OUTER JOIN pg_catalog.pg_type et ON et.oid=t.typelem
LEFT OUTER JOIN pg_catalog.pg_class c ON c.oid=t.typrelid
LEFT OUTER JOIN pg_catalog.pg_description d ON t.oid=d.objoid
WHERE t.typname IS NOT NULL
AND (c.relkind IS NULL OR c.relkind = $2) AND (et.typcategory IS NULL OR et.typcategory <> $3)
|
|
0 min
< 0.1%
|
26 ms
|
558
gateway-pg-user
|
with onboarding as (
select *
from onboarding_merchants
where onboarding_token = $1
)
select *
from (
SELECT
evt.*, ROW_NUMBER() OVER (PARTITION BY evt.step ORDER BY evt.created_at DESC) AS row_num
FROM onboarding
inner join public.onboarding_merchant_events evt on onboarding.id = evt.onboarding_id
order by evt.created_at desc
) last_list
where last_list.row_num = $2
|
|
0 min
< 0.1%
|
23 ms
|
623
gateway-pg-user
|
SELECT n.nspname AS table_schema, c.relname AS table, attname AS column, format_type(a.atttypid, a.atttypmod) AS column_type, pg_get_expr(d.adbin, d.adrelid) AS default_value FROM pg_catalog.pg_attribute a INNER JOIN pg_catalog.pg_class c ON c.oid = a.attrelid INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace INNER JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum) = (d.adrelid, d.adnum) WHERE NOT a.attisdropped AND a.attnum > $1 AND pg_get_expr(d.adbin, d.adrelid) LIKE $2 AND n.nspname NOT LIKE $3 /*pghero*/
|
|
0 min
< 0.1%
|
57 ms
|
245
gateway-pg-user
|
select to_json(json_build_array(((__local_0__."id")::numeric)::text)) as "__identifiers", to_json(( select json_build_object($5::text, (
select json_build_object($6::text, (json_build_object($7::text, sum($8), $9::text, (json_build_object($10::text, coalesce(sum(__local_1__."amount"), $11))), $12::text, (json_build_object($13::text, avg(__local_1__."amount"))), $14::text, (json_build_object($15::text, count(distinct __local_1__."id"))))))
from "public"."transactions" as __local_1__
where (__local_1__."merchant_id" = __local_0__."id") and (__local_1__."business_unit_id" = $1) and (((__local_1__."created_at" <= $2) and (__local_1__."created_at" >= $3)))
)) )) as "@totalRevenue"
from "public"."merchants" as __local_0__
where (__local_0__."merchant_token" = $4) and ($16) and ($17)
|
|
0 min
< 0.1%
|
62 ms
|
184
gateway-pg-user
|
-- Metabase:: userID: 13 queryType: MBQL queryHash: a921ad42ea6db70e2a3334d8d040d7f3d618ae3140477afaddb13e9e2cc1fb64
SELECT "source"."merchant_name" AS "merchant_name", "source"."business_unit_name" AS "business_unit_name", "source"."transaction_token" AS "transaction_token", "source"."payment_method" AS "payment_method", "source"."card_type" AS "card_type", "source"."amount" AS "amount", "source"."paid_amount" AS "paid_amount", "source"."status" AS "status", "source"."status_reason" AS "status_reason", "source"."created_at" AS "created_at", "source"."card_brand" AS "card_brand", "source"."card_pan" AS "card_pan" FROM (SELECT
m.name AS merchant_name,
mbu.name AS business_unit_name,
t.transaction_token,
pm.payment_method_name AS payment_method,
CASE
WHEN LOWER(pm.payment_method_name) LIKE $1 THEN $2
WHEN LOWER(pm.payment_method_name) LIKE $3 THEN $4
ELSE $5
END AS card_type,
t.amount,
t.paid_amount,
t.status,
t.status_reason,
t.created_at,
t.card_brand,
t.card_pan
FROM
transactions t
JOIN
payment_methods pm
ON t.payment_method_id = pm.id
JOIN
merchants m
ON t.merchant_id = m.id
JOIN
merchant_business_units mbu
ON t.business_unit_id = mbu.id
WHERE
t.merchant_id = $6) AS "source" LIMIT $7
|