Summary Queries Management

Create New
Summary Queries
Name Source Table Description Dimensions Metrics Status Actions
coll_aging_summary evolved_records Summary of collections by aging bucket coll_aging_bucket COUNT * SUM coll_total_due SUM raw_data.collected_amount AVG coll_recovery_rate AVG coll_risk_score Active
coll_priority_summary evolved_records Summary of collections by priority level coll_priority_level COUNT * SUM coll_total_due SUM raw_data.collected_amount AVG coll_recovery_rate AVG coll_risk_score Active
coll_strategy_summary evolved_records Summary of collections by recommended strategy coll_recommended_strategy COUNT * SUM coll_total_due AVG coll_risk_score AVG coll_recovery_rate AVG coll_days_overdue Active
coll_region_summary evolved_records Summary of collections by region coll_region_code raw_data.state COUNT * SUM coll_total_due SUM raw_data.collected_amount AVG coll_recovery_rate AVG coll_risk_score Active
coll_agency_performance evolved_records Performance metrics by agency coll_agency_code raw_data.agency_name COUNT * SUM coll_total_due SUM raw_data.collected_amount AVG coll_recovery_rate AVG coll_efficiency_score SUM coll_commission_base_amount Active
coll_product_analysis evolved_records Collections performance by product group coll_product_group raw_data.product COUNT * SUM coll_total_due SUM raw_data.collected_amount AVG coll_recovery_rate AVG coll_risk_score AVG coll_days_overdue Active
coll_channel_analysis evolved_records Collections performance by channel coll_channel_type COUNT * SUM coll_total_due SUM raw_data.collected_amount AVG coll_recovery_rate AVG coll_efficiency_score SUM coll_commission_base_amount Active
coll_special_case_analysis evolved_records Analysis of special cases (settlements, deaths) coll_special_case_type coll_is_special_case COUNT * SUM coll_total_due SUM raw_data.collected_amount AVG coll_settlement_discount SUM coll_commission_base_amount Active
coll_settlement_analysis evolved_records Detailed analysis of settlement cases coll_installment_type coll_bom_bck_club COUNT * SUM coll_total_due SUM raw_data.settlement_amount AVG coll_settlement_discount AVG coll_settlement_percentage SUM coll_commission_base_amount Active
coll_daily_trends evolved_records Daily trends in collections performance DATE(created_at) coll_aging_bucket COUNT * SUM coll_total_due SUM raw_data.collected_amount AVG coll_recovery_rate Active
coll_weekly_trends evolved_records Weekly trends in collections performance DATE_TRUNC('week', created_at) coll_priority_level COUNT * SUM coll_total_due SUM raw_data.collected_amount AVG coll_recovery_rate AVG coll_efficiency_score Active
coll_monthly_trends evolved_records Monthly trends in collections performance DATE_TRUNC('month', created_at) coll_product_group COUNT * SUM coll_total_due SUM raw_data.collected_amount AVG coll_recovery_rate COUNT CASE WHEN coll_is_special_case THEN 1 END SUM coll_commission_base_amount Active
coll_multidimensional_analysis evolved_records Multi-dimensional analysis combining agency, product, and region coll_agency_code coll_product_group coll_region_code COUNT * SUM coll_total_due SUM raw_data.collected_amount AVG coll_recovery_rate AVG coll_efficiency_score AVG coll_risk_score SUM coll_commission_base_amount Active
coll_template_eligibility_analysis evolved_records Analysis of template eligibility across different payout types coll_product_group coll_aging_bucket COUNT * COUNT CASE WHEN JSON_EXTRACT(coll_template_eligibility, '$.fixed') = TRUE THEN 1 END COUNT CASE WHEN JSON_EXTRACT(coll_template_eligibility, '$.additional') = TRUE THEN 1 END COUNT CASE WHEN JSON_EXTRACT(coll_template_eligibility, '$.settlement') = TRUE THEN 1 END COUNT CASE WHEN JSON_EXTRACT(coll_template_eligibility, '$.deduction') = TRUE THEN 1 END SUM coll_emi_capping_amount AVG coll_emi_capping_amount Active
coll_commission_readiness evolved_records Analysis of data readiness for commission calculations coll_agency_code raw_data.agency_name COUNT * COUNT CASE WHEN coll_agent_code != 'UNKNOWN' THEN 1 END COUNT CASE WHEN coll_commission_base_amount > 0 THEN 1 END COUNT CASE WHEN coll_negative_allowed = false AND coll_commission_base_amount < 0 THEN 1 END SUM coll_commission_base_amount AVG coll_emi_capping_amount COUNT CASE WHEN coll_is_special_case THEN 1 END Active
coll_product_summary evolved_records Summary of collections by product group coll_product_group raw_data.product COUNT * SUM coll_total_due SUM raw_data.collected_amount AVG coll_recovery_rate AVG coll_days_overdue Active
coll_channel_summary evolved_records Summary of collections by channel type coll_channel_type raw_data.area_type COUNT * SUM coll_total_due SUM raw_data.collected_amount AVG coll_recovery_rate AVG coll_efficiency_score Active
coll_special_case_summary evolved_records Summary of special collection cases coll_special_case_type COUNT * SUM coll_total_due SUM raw_data.collected_amount AVG coll_recovery_rate Active
coll_settlement_summary evolved_records Summary of settlement cases raw_data.subcategory COUNT * SUM coll_total_due SUM raw_data.settlement_amount AVG coll_settlement_discount Active
coll_performance_summary evolved_records Summary of collection performance categories coll_performance_category COUNT * SUM coll_total_due SUM raw_data.collected_amount AVG coll_recovery_rate AVG coll_efficiency_score Active
coll_region_by_product evolved_records Analysis of collections by region and product coll_region_code coll_product_group COUNT * SUM coll_total_due SUM raw_data.collected_amount AVG coll_recovery_rate Active
coll_channel_by_priority evolved_records Analysis of collections by channel and priority level coll_channel_type coll_priority_level COUNT * SUM coll_total_due SUM raw_data.collected_amount AVG coll_recovery_rate Active
coll_agency_by_aging evolved_records Analysis of agency performance by aging bucket coll_agency_code coll_aging_bucket COUNT * SUM coll_total_due SUM raw_data.collected_amount AVG coll_recovery_rate Active
coll_monthly_trend evolved_records Monthly trend analysis of collections raw_data.month raw_data.year COUNT * SUM coll_total_due SUM raw_data.collected_amount AVG coll_recovery_rate Active
coll_weekly_trend evolved_records Weekly trend analysis of collections raw_data.week raw_data.month raw_data.year COUNT * SUM coll_total_due SUM raw_data.collected_amount AVG coll_recovery_rate Active
coll_daily_trend evolved_records Daily trend analysis of collections raw_data.day raw_data.month raw_data.year COUNT * SUM coll_total_due SUM raw_data.collected_amount AVG coll_recovery_rate Active
coll_day_of_week_trend evolved_records Analysis of collections by day of week raw_data.day_of_week COUNT * SUM coll_total_due SUM raw_data.collected_amount AVG coll_recovery_rate Active
coll_region_weekly_trend evolved_records Weekly trend analysis by region coll_region_code raw_data.week raw_data.month raw_data.year COUNT * SUM coll_total_due SUM raw_data.collected_amount AVG coll_recovery_rate Active
coll_agent_weekly_trend evolved_records Weekly trend analysis by agent coll_agent_code raw_data.week raw_data.month raw_data.year COUNT * SUM coll_total_due SUM raw_data.collected_amount AVG coll_recovery_rate Active
coll_channel_day_of_week evolved_records Analysis of channel performance by day of week coll_channel_type raw_data.day_of_week COUNT * SUM coll_total_due SUM raw_data.collected_amount AVG coll_recovery_rate Active
coll_agent_performance evolved_records Performance metrics by collection agent coll_agent_code coll_agency_code COUNT * SUM coll_total_due SUM raw_data.collected_amount AVG coll_recovery_rate AVG coll_efficiency_score Active
© SimSol Technologies and Services Private Limited