Business Intelligence & Reports

{
  title: "High-Value Customers" as const,
  dateRangePicker: false,
  sql: (fromDate: Date, toDate: Date) => sql`
    SELECT
      c.full_name AS "Customer Name",
      c.email AS "Email",
      COUNT(DISTINCT p.id) AS "Successful Payments",
      SUM(
        (line_items->>'amount')::numeric
      ) AS "Total Revenue",
      MAX(p.created) AS "Last Payment Date"
    FROM order_customer c
    JOIN order_order o ON o.customer_id_id = c.id
    JOIN order_payment p ON p.order_id_id = o.id
    WHERE p.payment_status = 'settled'
    GROUP BY c.id, c.full_name, c.email
    HAVING COUNT(DISTINCT p.id) >= 5
    ORDER BY "Total Revenue" DESC
  `
}

{
  "title": "subscription_status_snapshot",
  "data": [
    {"subscription_status": "active", "count": 1247},
    {"subscription_status": "past_due", "count": 89},
    {"subscription_status": "on_hold", "count": 45}
  ]
}

{
  "title": "customer_count",
  "data": 3542
}

SELECT
  s.timestamp as date,
  s.entry->>'subscription_status' as status,
  s.entry->>'count' as count
FROM (
  SELECT t.timestamp, jsonb_array_elements(t.data) as entry
  FROM statistic_timestampevent t
  WHERE t.title = 'subscription_status_snapshot'
) as s
WHERE s.entry->>'subscription_status' IN ('active', 'past_due', 'on_hold')
  AND s.timestamp >= '2024-01-01'
ORDER BY s.timestamp

# apps/statistic/views.py
def stats_per_tenant(tenant):
    with tenant_context(tenant):
        active_subscriptions = subscription.objects.filter(
            subscription_status='active'
        ).count()

        # Weekly deliveries
        start_this_week, end_this_week = get_start_end_of_week(today)
        deliveries_this_week = delivery.objects.filter(
            delivery_date__date__range=[start_this_week, end_this_week]
        ).count()

        # ... more metrics

    return {
        'active_subscriptions': active_subscriptions,
        'deliveries_this_week': deliveries_this_week,
        # ... more data
    }