Okul.com.tr — Eğitim Ekosistemi

Okul Analiz Sorguları

2 dakika okuma süresi

Proje: Okul Database · Hub: Okul Database — Queries

Okul Analiz Sorguları

Temel Sayımlar

-- Toplam aktif okul
SELECT COUNT(*) FROM school_filters
WHERE deleted_at IS NULL AND is_active = 1;
 
-- Müşteri olan vs olmayan
SELECT is_customer, COUNT(*) as count
FROM school_filters
WHERE deleted_at IS NULL AND is_active = 1
GROUP BY is_customer;
 
-- Sektör bazlı dağılım
SELECT sf.sector_id, s.name as sector, COUNT(*) as count
FROM school_filters sf
JOIN sectors s ON s.id = sf.sector_id
WHERE sf.deleted_at IS NULL AND sf.is_active = 1
GROUP BY sf.sector_id, s.name
ORDER BY count DESC;

Coğrafi Analiz

-- İl bazlı aktif okul sayısı (Top 10)
SELECT city_name, COUNT(*) as school_count
FROM school_filters
WHERE deleted_at IS NULL AND is_active = 1
GROUP BY city_id, city_name
ORDER BY school_count DESC
LIMIT 10;
 
-- İl + müşteri bazlı dağılım
SELECT city_name,
       SUM(is_customer) as customer_schools,
       COUNT(*) - SUM(is_customer) as non_customer_schools,
       ROUND(SUM(is_customer) * 100.0 / COUNT(*), 1) as customer_pct
FROM school_filters
WHERE deleted_at IS NULL AND is_active = 1
GROUP BY city_id, city_name
ORDER BY customer_pct DESC;

Engagement Analizi

-- Engagement skorlarına göre Top okullar
SELECT school_id, city_name,
       lead_count, comment_count, view_count,
       py_profile_point
FROM school_filters
WHERE is_customer = 1 AND deleted_at IS NULL
ORDER BY lead_count DESC
LIMIT 20;
 
-- Günlük engagement trendi (son 30 gün)
SELECT date,
       SUM(page_view) as total_views,
       SUM(lead_count) as total_leads,
       SUM(phone_view) as total_phone_views
FROM school_daily_logs
WHERE date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY date
ORDER BY date;

Ücret Analizi

-- Sektör bazlı ortalama ücret aralığı
SELECT s.name as sector,
       AVG(sf.fee_min) as avg_min_fee,
       AVG(sf.fee_max) as avg_max_fee,
       MIN(sf.fee_min) as min_fee,
       MAX(sf.fee_max) as max_fee
FROM school_filters sf
JOIN sectors s ON s.id = sf.sector_id
WHERE sf.deleted_at IS NULL
  AND sf.fee_min > 0
GROUP BY sf.sector_id, s.name;

Yorum ve Rating

-- Ortalama okul puanı dağılımı
SELECT ROUND(rating) as rating_floor,
       COUNT(*) as review_count
FROM school_comments
WHERE deleted_at IS NULL AND approved_at IS NOT NULL
GROUP BY ROUND(rating)
ORDER BY rating_floor;
 
-- En çok yorum alan okullar
SELECT school_id, COUNT(*) as comment_count,
       AVG(rating) as avg_rating
FROM school_comments
WHERE deleted_at IS NULL AND approved_at IS NOT NULL
GROUP BY school_id
ORDER BY comment_count DESC
LIMIT 10;

Grafik Görünümü

Backlinkler