---

-- 1.1
SELECT COUNT(*) AS total_clients FROM clients;

-- 1.2
SELECT MAX(experience_years) AS max_experience FROM trainers;

-- 1.3
SELECT MIN(salary) AS min_salary FROM trainers;

-- 1.4
SELECT ROUND(AVG(duration_minutes)) AS avg_duration FROM sessions;

-- 1.5
SELECT SUM(price) AS total_revenue FROM sessions;

-- 2.1
SELECT membership_type, COUNT(*) AS client_count
FROM clients
GROUP BY membership_type;

-- 2.2
SELECT t.name, MIN(s.price) AS min_price, MAX(s.price) AS max_price
FROM sessions s
JOIN trainers t ON s.trainer_id = t.id
GROUP BY t.id, t.name;

-- 2.3
SELECT t.name, SUM(s.duration_minutes) AS total_minutes
FROM sessions s
JOIN trainers t ON s.trainer_id = t.id
GROUP BY t.id, t.name
ORDER BY total_minutes DESC;

-- 2.4
SELECT gender, FLOOR(AVG(TIMESTAMPDIFF(YEAR, birth_date, CURDATE()))) AS avg_age
FROM clients
GROUP BY gender;

-- 3.1
SELECT t.specialization, c.gender, COUNT(*) AS session_count
FROM sessions s
JOIN trainers t ON s.trainer_id = t.id
JOIN clients c ON s.client_id = c.id
GROUP BY t.specialization, c.gender
ORDER BY t.specialization, c.gender;

-- 3.2
SELECT t.name, c.membership_type, SUM(s.price) AS total_price
FROM sessions s
JOIN trainers t ON s.trainer_id = t.id
JOIN clients c ON s.client_id = c.id
GROUP BY t.id, t.name, c.membership_type
ORDER BY t.name, total_price DESC;

-- 4.1
SELECT t.name, AVG(s.price) AS avg_price
FROM sessions s
JOIN trainers t ON s.trainer_id = t.id
GROUP BY t.id, t.name
HAVING AVG(s.price) > 1600;

-- 4.2
SELECT specialization, COUNT(*) AS trainer_count
FROM trainers
GROUP BY specialization
HAVING COUNT(*) > 1;

-- 4.3
SELECT c.name, COUNT(*) AS session_count
FROM sessions s
JOIN clients c ON s.client_id = c.id
GROUP BY c.id, c.name
HAVING COUNT(*) > 2;

-- 4.4
SELECT membership_type, FLOOR(AVG(TIMESTAMPDIFF(YEAR, birth_date, CURDATE()))) AS avg_age
FROM clients
GROUP BY membership_type
HAVING AVG(TIMESTAMPDIFF(YEAR, birth_date, CURDATE())) < 35;

-- 5.1
SELECT t.name, SUM(s.price) AS total_revenue
FROM sessions s
JOIN trainers t ON s.trainer_id = t.id
WHERE s.session_date >= '2025-03-01' AND s.session_date < '2025-04-01'
GROUP BY t.id, t.name
HAVING SUM(s.price) > 4000;

-- 5.2
SELECT c.membership_type, AVG(s.duration_minutes) AS avg_duration
FROM sessions s
JOIN clients c ON s.client_id = c.id
WHERE c.gender = 'Ж'
GROUP BY c.membership_type
HAVING AVG(s.duration_minutes) > 60;

-- 6.1
SELECT COUNT(DISTINCT client_id) AS unique_clients FROM sessions;

-- 6.2
SELECT t.name, COUNT(DISTINCT s.client_id) AS unique_clients
FROM sessions s
JOIN trainers t ON s.trainer_id = t.id
GROUP BY t.id, t.name
ORDER BY unique_clients DESC;

-- 7.1
SELECT 
    DATE_FORMAT(session_date, '%Y-%m') AS month, 
    COUNT(*) AS session_count, 
    SUM(price) AS total_revenue
FROM sessions
WHERE YEAR(session_date) = 2025
GROUP BY DATE_FORMAT(session_date, '%Y-%m')
ORDER BY month;

-- 7.2
SELECT 
    DATE_FORMAT(session_date, '%Y-%m') AS month, 
    ROUND(AVG(duration_minutes)) AS avg_duration
FROM sessions
WHERE YEAR(session_date) = 2025
GROUP BY DATE_FORMAT(session_date, '%Y-%m')
ORDER BY month;

-- 8.1
SELECT t.name, COUNT(*) AS session_count
FROM sessions s
JOIN trainers t ON s.trainer_id = t.id
GROUP BY t.id, t.name
ORDER BY session_count DESC
LIMIT 1;

-- 8.2
SELECT c.name, SUM(s.price) AS total_spent
FROM sessions s
JOIN clients c ON s.client_id = c.id
GROUP BY c.id, c.name
ORDER BY total_spent DESC
LIMIT 1;

-- 8.3
SELECT t.specialization, SUM(s.price) AS total_revenue
FROM sessions s
JOIN trainers t ON s.trainer_id = t.id
WHERE s.session_date >= '2025-04-01' AND s.session_date < '2025-05-01'
GROUP BY t.specialization
ORDER BY total_revenue DESC
LIMIT 1;