7 142
Subscribers
-224 hours
-327 days
-11830 days
Posts Archive
WITH connection_usage AS (
SELECT
connection_ref,
COALESCE(SUM(internet_mb), 0) / 1024.0 AS connection_internet_gb
FROM daily_service_consumption
WHERE consumption_date >= '2024-02-01' AND consumption_date < '2024-03-01'
GROUP BY connection_ref
),
bill_details AS (
SELECT
b.bill_ref,
b.connection_ref,
b.payable_amount,
LEAST(b.payable_amount, COALESCE(SUM(CASE WHEN t.transaction_state = 'SUCCESS' THEN t.transaction_amount ELSE 0 END), 0)) AS bill_collected_amount
FROM monthly_service_bills b
LEFT JOIN bill_transactions t ON b.bill_ref = t.bill_ref
WHERE b.cycle_month = '2024-02-01' AND b.bill_state != 'CANCELLED'
GROUP BY b.bill_ref, b.connection_ref, b.payable_amount
),
connection_bills AS (
SELECT
connection_ref,
SUM(payable_amount) AS total_conn_bill_amount,
SUM(bill_collected_amount) AS total_conn_collected_amount
FROM bill_details
GROUP BY connection_ref
)
SELECT
p.package_ref,
p.package_label,
COUNT(DISTINCT mc.connection_ref) AS active_connection_count,
ROUND(SUM(COALESCE(cu.connection_internet_gb, 0)), 2) AS total_internet_gb,
ROUND(SUM(cb.total_conn_bill_amount), 2) AS total_bill_amount,
ROUND(SUM(cb.total_conn_collected_amount), 2) AS total_collected_amount,
ROUND(SUM(cb.total_conn_bill_amount) - SUM(cb.total_conn_collected_amount), 2) AS pending_amount,
ROUND(
CASE
WHEN SUM(cb.total_conn_bill_amount) = 0 THEN 0.00
ELSE (SUM(cb.total_conn_collected_amount) / SUM(cb.total_conn_bill_amount)) * 100
END,
2
) AS collection_precentage
FROM service_packages p
INNER JOIN mobile_connections mc ON p.package_ref = mc.package_ref
INNER JOIN connection_bills cb ON mc.connection_ref = cb.connection_ref
LEFT JOIN connection_usage cu ON mc.connection_ref = cu.connection_ref
WHERE mc.connection_state = 'ACTIVE'
AND p.package_enabled = 1
GROUP BY p.package_ref, p.package_label
ORDER BY collection_precentage DESC, p.package_label ASC;
FLIPKART✅
SQL✅
if score >= 10:
risk_level = "HIGH"
elif score >= 6:
risk_level = "MEDIUM"
else:
risk_level = "LOW"
if risk_level in ("HIGH", "MEDIUM"):
output_regions.append({
"name": region_name,
"risk_level": risk_level,
"score": score,
"active_deployments": active_deployments,
"input_index": input_index
})
if not output_regions:
print("NA")
return
output_regions.sort(key=lambda x: (
0 if x["risk_level"] == "HIGH" else 1,
-x["score"],
x["active_deployments"],
x["input_index"]
))
formatted_strings = [
f"{r['name']}-{r['risk_level']}-{r['score']}-{r['active_deployments']}"
for r in output_regions
]
print("#".join(formatted_strings))
FLIPART✅
DATAFRAME✅
def process_usage_logs(usage_records, reference_day, region_details, profiles, region_stats):
for record in usage_records:
usage_id, region_id, usage_day, cpu_usage, memory_usage = record
usage_day = int(usage_day)
cpu_usage = int(cpu_usage)
memory_usage = int(memory_usage)
if region_id not in region_details:
continue
if not (1 <= usage_day <= reference_day):
continue
if cpu_usage < 0 or memory_usage < 0:
continue
region_type = region_details[region_id][1]
max_cpu, max_mem = profiles[region_type]
stats = region_stats[region_id]
stats["valid_usage_count"] += 1
stats["cpu_sum"] += cpu_usage
stats["memory_sum"] += memory_usage
if cpu_usage > max_cpu:
stats["cpu_breach_count"] += 1
if memory_usage > max_mem:
stats["memory_breach_count"] += 1
def process_deployments(deployment_records, reference_day, region_details, region_stats):
for record in deployment_records:
deployment_id, region_id, deployment_day, deployment_status = record
deployment_day = int(deployment_day)
if region_id not in region_details:
continue
if deployment_status not in VALID_DEPLOYMENT_STATUSES:
continue
if not (1 <= deployment_day <= reference_day):
continue
if deployment_status == "ACTIVE":
region_stats[region_id]["active_deployment_count"] += 1
def process_incident_reports(incident_records, reference_day, region_details, region_stats):
for record in incident_records:
incident_id, region_id, incident_day, severity = record
incident_day = int(incident_day)
if region_id not in region_details:
continue
if severity not in VALID_INCIDENT_SEVERITIES:
continue
if not (1 <= incident_day <= reference_day):
continue
if severity == "HIGH":
region_stats[region_id]["high_severity_incident_count"] += 1
def calculate_type_average_cpu(profiles, type_cpu_sum, type_usage_count):
type_average_cpu = {}
for region_type in profiles:
count = type_usage_count.get(region_type, 0)
if count == 0:
type_average_cpu[region_type] = 0
else:
type_average_cpu[region_type] = type_cpu_sum.get(region_type, 0) // count
return type_average_cpu
def calculate_region_risk(stats, max_memory_usage, type_average_cpu):
pass
def build_output(regions, profiles, region_stats, type_average_cpu):
output_regions = []
for region_id, region_name, region_type, input_index in regions:
stats = region_stats[region_id]
max_cpu, max_mem = profiles[region_type]
valid_count = stats["valid_usage_count"]
if valid_count == 0:
avg_cpu = 0
avg_mem = 0
else:
avg_cpu = stats["cpu_sum"] // valid_count
avg_mem = stats["memory_sum"] // valid_count
active_deployments = stats["active_deployment_count"]
cpu_breaches = stats["cpu_breach_count"]
mem_breaches = stats["memory_breach_count"]
high_incidents = stats["high_severity_incident_count"]
type_avg_cpu = type_average_cpu.get(region_type, 0)
is_above_cpu_baseline = (valid_count > 0 and avg_cpu > type_avg_cpu)
score = 0
if active_deployments < 2:
score += 4
if active_deployments == 0:
score += 3
if cpu_breaches >= 2:
score += 3
if mem_breaches >= 2:
score += 3
if high_incidents >= 1:
score += 3
if is_above_cpu_baseline:
score += 2
if valid_count > 0 and avg_mem > max_mem:
score += 2
+1
QUANTIPHI ROUND-2 VIBE CODING✅✅
ROUND-1 CLEARED✅
Contact : @MLCODER2
