Okul.com.tr — Eğitim Ekosistemi

Gelir Analiz Sorguları

2 dakika okuma süresi

Proje: Okul Database · Hub: Okul Database — Queries

Gelir Analiz Sorguları

Temel Gelir

-- Toplam tahsilat
SELECT SUM(amount) as total_revenue,
       COUNT(*) as payment_count
FROM payments
WHERE deleted_at IS NULL
  AND paid_at IS NOT NULL
  AND status = 'paid';  -- status enum değerini tinker ile doğrula
 
-- Aylık gelir trendi
SELECT DATE_FORMAT(paid_at, '%Y-%m') as month,
       SUM(amount) as revenue,
       COUNT(*) as transactions,
       COUNT(DISTINCT customer_agreement_id) as agreements
FROM payments
WHERE deleted_at IS NULL AND paid_at IS NOT NULL
GROUP BY month
ORDER BY month;

Sözleşme Analizi

-- Aktif sözleşme sayısı
SELECT COUNT(*) FROM customer_agreements
WHERE deleted_at IS NULL AND status = 'active';
 
-- Sözleşme başına ortalama değer
SELECT AVG(total) as avg_agreement_value FROM (
  SELECT customer_agreement_id, SUM(amount) as total
  FROM payments
  WHERE deleted_at IS NULL AND paid_at IS NOT NULL
  GROUP BY customer_agreement_id
) sub;
 
-- Müşteri başına gelir (Top 20)
SELECT ca.customer_id,
       c.name as customer_name,
       COUNT(DISTINCT ca.id) as agreements,
       SUM(p.amount) as total_paid
FROM customer_agreements ca
JOIN customers c ON c.id = ca.customer_id
JOIN payments p ON p.customer_agreement_id = ca.id
WHERE ca.deleted_at IS NULL
  AND p.deleted_at IS NULL
  AND p.paid_at IS NOT NULL
GROUP BY ca.customer_id, c.name
ORDER BY total_paid DESC
LIMIT 20;

Ürün Mix Analizi

-- Ürün bazlı sözleşme dağılımı
SELECT pp.product_id,
       pr.name as product_name,
       COUNT(ca.id) as agreement_count,
       SUM(p.amount) as total_revenue
FROM customer_agreements ca
JOIN product_prices pp ON pp.id = ca.product_price_id
JOIN products pr ON pr.id = pp.product_id
LEFT JOIN payments p ON p.customer_agreement_id = ca.id
  AND p.deleted_at IS NULL AND p.paid_at IS NOT NULL
WHERE ca.deleted_at IS NULL
GROUP BY pp.product_id, pr.name
ORDER BY total_revenue DESC;

Gecikmiş Ödemeler

-- Vadesi geçmiş ama ödenmemiş
SELECT COUNT(*) as overdue_count,
       SUM(amount) as overdue_amount
FROM payments
WHERE deleted_at IS NULL
  AND paid_at IS NULL
  AND due_date < CURDATE();  -- kolon adını tinker ile doğrula

Yenileme Analizi

-- Yenileme indirim durumları
SELECT followed_status, COUNT(*) as count
FROM customer_agreements_renew_discounts
WHERE deleted_at IS NULL
GROUP BY followed_status;

⚠️ Doğrulama Notu

payments.status enum değerlerini ve due_date kolon adını çalıştırmadan önce tinker ile doğrula:

// Tinker'da
\App\Models\Payment::first()->toArray();
\DB::select('SHOW COLUMNS FROM payments');

Grafik Görünümü

Backlinkler