---

-- Задача 1

SELECT
    genre,
    COUNT(*) AS books_count,
    STRING_AGG(title, ', ' ORDER BY title) AS books_list
FROM books
GROUP BY genre
ORDER BY books_count DESC, genre;


-- Задача 2

SELECT
    l.loan_id,
    r.full_name,
    b.title,
    l.loan_date,
    (l.loan_date + INTERVAL '14 days')::date AS return_deadline,
    (l.loan_date - INTERVAL '3 days')::date AS three_days_before,
    CURRENT_DATE - l.loan_date AS days_since_loan,
    (l.loan_date + INTERVAL '14 days')::date
        - CURRENT_DATE AS days_until_deadline,
    TO_CHAR(l.loan_date, 'TMMonth YYYY') AS month_year
FROM loans AS l
JOIN readers AS r ON r.reader_id = l.reader_id
JOIN copies AS c ON c.copy_id = l.copy_id
JOIN books AS b ON b.book_id = c.book_id
ORDER BY l.loan_date, l.loan_id;


-- Задача 3

SELECT
    reader_id,
    full_name,
    SPLIT_PART(full_name, ' ', 2)
        || ' '
        || LEFT(SPLIT_PART(full_name, ' ', 1), 1)
        || '.' AS short_name,
    UPPER(email) AS email_upper,
    CASE
        WHEN POSITION('@' IN email) > 0
        THEN SUBSTRING(email FROM POSITION('@' IN email) + 1)
        ELSE NULL
    END AS email_domain
FROM readers
ORDER BY reader_id;


-- Задача 4

SELECT
    r.reader_id,
    r.full_name,
    COALESCE(r.phone, 'Не указан') AS phone,
    COALESCE(r.email, 'Не указан') AS email,
    COUNT(l.loan_id) AS loans_count,
    CASE
        WHEN COUNT(l.loan_id) = 0 THEN 'Низкая'
        WHEN COUNT(l.loan_id) BETWEEN 1 AND 2 THEN 'Средняя'
        ELSE 'Высокая'
    END AS activity
FROM readers AS r
LEFT JOIN loans AS l ON l.reader_id = r.reader_id
GROUP BY
    r.reader_id,
    r.full_name,
    r.phone,
    r.email
ORDER BY loans_count DESC, r.reader_id;


-- Задача 5

WITH book_statistics AS (
    SELECT
        b.book_id,
        b.title,
        b.genre,
        COUNT(l.loan_id) AS loans_count
    FROM books AS b
    LEFT JOIN copies AS c ON c.book_id = b.book_id
    LEFT JOIN loans AS l ON l.copy_id = c.copy_id
    GROUP BY b.book_id, b.title, b.genre
)
SELECT
    book_id,
    title,
    genre,
    loans_count,
    ROW_NUMBER() OVER (
        ORDER BY loans_count DESC, book_id
    ) AS overall_place,
    ROW_NUMBER() OVER (
        PARTITION BY genre
        ORDER BY loans_count DESC, book_id
    ) AS genre_place
FROM book_statistics
ORDER BY overall_place;