REGISTRATIONS PER DAY

SELECT CAST("regprc"."registration"."upd_dtimes" AS date) AS "upd_dtimes", count(*) AS "count"
FROM "regprc"."registration" WHERE ("regprc"."registration"."status_code" = 'PROCESSED'
AND ("regprc"."registration"."pkt_cr_dtimes" >= timestamp with time zone '2021-06-01 00:00:00.000Z' AND "regprc"."registration"."pkt_cr_dtimes" < timestamp with time zone '2021-08-01 00:00:00.000Z'))
GROUP BY CAST("regprc"."registration"."upd_dtimes" AS date) ORDER BY CAST("regprc"."registration"."upd_dtimes" AS date) ASC

REGISTRATION PER TIME OF THE WEEK

SELECT To_Char("rg"."pkt_cr_dtimes", 'DAY') as "DAY",date_part('hours',"rg"."pkt_cr_dtimes")+3 hours, count(*) no_reg
FROM "regprc"."registration" as rg
Group by date("rg"."pkt_cr_dtimes"),date_part('hours',"rg"."pkt_cr_dtimes"),date_part('dow',"rg"."pkt_cr_dtimes"),To_Char("rg"."pkt_cr_dtimes", 'DAY')
having round(EXTRACT(epoch from (max("rg"."pkt_cr_dtimes")- min("rg"."pkt_cr_dtimes")))) >1500
Order by date_part('dow',"rg"."pkt_cr_dtimes"), CASE
WHEN date_part('hours',"rg"."pkt_cr_dtimes")+3 >= 6 THEN date_part('hours',"rg"."pkt_cr_dtimes")
ELSE date_part('hours',"rg"."pkt_cr_dtimes") +3 + 24
END

FAILED REGISTRATION STATUS ON STAGES

SELECT "Trn Type Code"."descr" AS "descr", "regprc"."registration_transaction"."reg_id" AS "reg_id", "regprc"."registration_transaction"."status_code" AS "status_code",max("regprc"."registration_transaction"."cr_dtimes")
FROM "regprc"."registration_transaction"
LEFT JOIN "regprc"."registration" "Reg" ON "regprc"."registration_transaction"."reg_id" = "Reg"."id" LEFT JOIN "regprc"."transaction_type" "Trn Type Code" ON "regprc"."registration_transaction"."trn_type_code" = "Trn Type Code"."code"
WHERE ("Reg"."status_code" = 'FAILED')
GROUP BY "Trn Type Code"."descr", "regprc"."registration_transaction"."reg_id", "regprc"."registration_transaction"."status_code"
ORDER BY "Trn Type Code"."descr" ASC, "regprc"."registration_transaction"."reg_id" ASC, "regprc"."registration_transaction"."status_code" ASC
LIMIT 1048576

PROCESSING REGISTRATION STATUS ON STAGES

SELECT "Trn Type Code"."descr" AS "descr", "regprc"."registration_transaction"."reg_id" AS "reg_id", "regprc"."registration_transaction"."status_code" AS "status_code",max("regprc"."registration_transaction"."cr_dtimes")
FROM "regprc"."registration_transaction"
LEFT JOIN "regprc"."registration" "Reg" ON "regprc"."registration_transaction"."reg_id" = "Reg"."id" LEFT JOIN "regprc"."transaction_type" "Trn Type Code" ON "regprc"."registration_transaction"."trn_type_code" = "Trn Type Code"."code"
WHERE ("Reg"."status_code" = 'PROCESSING')
GROUP BY "Trn Type Code"."descr", "regprc"."registration_transaction"."reg_id", "regprc"."registration_transaction"."status_code"
ORDER BY "Trn Type Code"."descr" ASC, "regprc"."registration_transaction"."reg_id" ASC, "regprc"."registration_transaction"."status_code" ASC
LIMIT 1048576

AVERAGE DURATION PER STAGE

Select trn_type_code,MAX(duration_s),Min(duration_s),
CASE when avg(duration_s)>5000 then 5000 else avg(duration_s) end average from(
{{snippet: s}}) as d Group by trn_type_code
order by average Desc

REGISTRATION count PER STAGE

SELECT "regprc"."registration"."latest_trn_type_code" AS "latest_trn_type_code", "regprc"."registration"."status_code" AS "status_code", count(*) AS "count"
FROM "regprc"."registration"
GROUP BY "regprc"."registration"."latest_trn_type_code", "regprc"."registration"."status_code"
ORDER BY "regprc"."registration"."latest_trn_type_code" ASC, "regprc"."registration"."status_code" ASC