sorgular.sql

-- Hangi ulkede kac musteri var.
SELECT country AS ulkeler, COUNT(*) AS musteri_sayisi
FROM customers
GROUP BY country
ORDER BY musteri_sayisi DESC;

-- En cok siparis veren musteri
SELECT c.company_name AS sirket_adi, COUNT(o.order_id) AS siparis_sayisi
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.company_name
ORDER BY siparis_sayisi DESC
LIMIT 5;

-- En pahali urun
SELECT product_name AS urun_adi, unit_price AS urun_fiyati
FROM products
ORDER BY unit_price DESC
LIMIT 1;

-- En yuksek ciro yapan müsteri
SELECT c.company_name AS sirket_adi,
       SUM(od.unit_price * od.quantity * (1 - od.discount)) AS toplam_harcama
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_details od ON o.order_id = od.order_id
GROUP BY c.company_name
ORDER BY toplam_harcama DESC
LIMIT 1;

-- Calisanlarin aldigi siparis sayisi -  Calisan performansi 
SELECT e.first_name || ' ' || e.last_name AS calisan,
       COUNT(o.order_id) AS siparis_sayisi
FROM employees e
JOIN orders o ON e.employee_id = o.employee_id
GROUP BY calisan
ORDER BY siparis_sayisi DESC;

-- Hangi kargo firmasi en cok siparisi tasimis?
SELECT sh.company_name AS sirket_adi, COUNT(o.order_id) AS siparis_sayisi
FROM shippers sh
JOIN orders o ON sh.shipper_id = o.ship_via
GROUP BY sh.company_name
ORDER BY siparis_sayisi DESC;

-- Urun kategorisi bazinda satis toplami-Kategoriye gore ciro
SELECT cat.category_name AS kategori_adi,
       SUM(od.unit_price * od.quantity) AS toplam_satis
FROM categories cat
JOIN products p ON cat.category_id = p.category_id
JOIN order_details od ON p.product_id = od.product_id
GROUP BY cat.category_name
ORDER BY toplam_satis DESC;

-- En cok siparis edilen 5 urun ve onlari hangi musteriler siparis etmis?
SELECT p.product_name AS urun_adi, c.company_name AS sirket_adi, SUM(od.quantity) AS adet
FROM order_details od
JOIN products p ON od.product_id = p.product_id
JOIN orders o ON od.order_id = o.order_id
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY p.product_name, c.company_name
ORDER BY adet DESC
LIMIT 5;