= Напредни извештаи од базата (SQL и складирани процедури)
== Прикажување на совет чија што дата на побарување не е постара од 6 месеци гледано од денешната дата, прикажување на корисник кој го побарал советот и прикажување на број на одговори на прашањето.
{{{
SELECT
        bpc.id AS consultation_id,
        bpc.date_askes AS claim_date,
        bpc.title AS consultation_title,
        bpc.description AS consultation_description,
        u.id AS user_id,
        u.name AS user_name,
        COUNT(bpa.id) AS answers_count
    FROM
        project.blog_post_for_consultations bpc
    LEFT JOIN
        project.users u ON bpc.users_id = u.id
    LEFT JOIN
        project.blog_post_answers bpa ON bpc.id = bpa.blogpostconsid
    WHERE
        bpc.date_askes >= current_date - interval '6 months'
    GROUP BY
        bpc.id, bpc.date_askes, bpc.title, bpc.description, u.id, u.name
    ORDER BY
        answers_count DESC
    LIMIT 1;

}}} 
== Прикажување на вработен (доктор) кој што има напишено најмногу извештаи за едно исто милениче.
{{{
WITH DoctorReportsCount AS (
    SELECT
        u.id AS doctor_id,
        u.name AS doctor_name,
        r.petsID AS pet_id,
        COUNT(r.id) AS reports_count
    FROM
        project.reports r
    LEFT JOIN
        project.users u ON r.usersID = u.id
    GROUP BY
        u.id, u.name, r.petsID
    ORDER BY
        reports_count DESC
    LIMIT 1
)

SELECT
    drc.doctor_id,
    drc.doctor_name,
    drc.pet_id,
    drc.reports_count
FROM
    DoctorReportsCount drc;
}}}
== За секое прашање кое е поставено во Blog Post For Consultations колку одговори има, од кои корисници се дадени одговорите и каква е улогата на корисниците кои одговориле(admin, manager, employee, standard).
{{{
SELECT
    bpc.ID AS question_id,
    bpc.title AS question_title,
    COUNT(bpa.ID) AS number_of_answers,
    u."name" AS user_name,
    r.type AS user_role
FROM
    project.blog_post_for_consultations bpc
JOIN
    project.blog_post_answers bpa ON bpc.ID = bpa.blogpostconsid
JOIN
    project.users u ON bpa.usersID = u.ID
JOIN
    project.roles r ON u.role_id = r.ID
GROUP BY
    bpc.ID, u.ID, r.ID  -- Include the role ID in the GROUP BY clause
ORDER BY
    bpc.ID;
}}}
== Прикажување на продукт кој е прв додаден во системот за продажба и за него постојат најмалку нарачки кои биле направени од корисниците.
{{{
   SELECT
    p.id AS product_id,
    p.name AS product_name,
    p.description AS product_description,
    p.price AS product_price,
    MIN(o.quantity) AS sold_quantity,
    p.dateadded AS date_added
FROM
    project.products p
LEFT JOIN
    project.product_are_made_orders pamo ON p.id = pamo.id_products
LEFT JOIN
    project.orders o ON pamo.id_orders = o.id
GROUP BY
    p.id, p.name, p.description, p.price, p.dateadded
ORDER BY
    sold_quantity, date_added
LIMIT 1;
}}}
== Секој ветеринарен центар колку вкупно миленичиња примил и колку е просечната возраст во секој ветеринарен центар.
{{{
 SELECT
    vc.name AS vet_center_name,
  --  vc.description AS vet_center_description,
    COUNT(DISTINCT pvvc.id_pets) AS number_of_pets_treated,
    AVG(EXTRACT(YEAR FROM age(current_date, p.dateofbirthday))) AS average_pet_age
FROM
    project.vet_centers vc
JOIN
    project.pets_visit_vet_centers pvvc ON vc.id = pvvc.id_vet_centers
JOIN
    project.pets p ON pvvc.id_pets = p.id
GROUP BY
    vc.id, vc.name, vc.description
ORDER BY
    number_of_pets_treated DESC;
}}}
== Koja e најновата терапија според датум која е препишена, за која дијагноза е препишена, и во кој ветеринарен центар е препишена.
{{{
 WITH LatestTherapies AS (
    SELECT
        ptvc.id_pets,
        MAX(t.appoitmentDate) AS latest_therapy_date
    FROM
        project.pets_visit_vet_centers ptvc
    JOIN
        project.therapy t ON ptvc.id_pets = t.diagnosticsID
    GROUP BY
        ptvc.id_pets
)

SELECT
    vc.name AS vet_center_name,
    p.id AS pet_id,
    --p.description AS pet_description,
    d.description AS diagnosis_description,
    t.description AS latest_therapy_description,
    lt.latest_therapy_date
FROM
    project.vet_centers vc
JOIN
    project.pets_visit_vet_centers ptvc ON vc.id = ptvc.id_vet_centers
JOIN
    project.pets p ON ptvc.id_pets = p.id
JOIN
    project.diagnostics d ON p.id = d.usersID
LEFT JOIN
    LatestTherapies lt ON p.id = lt.id_pets
LEFT JOIN
    project.therapy t ON lt.latest_therapy_date = t.appoitmentDate AND p.id = t.diagnosticsID
ORDER BY
   latest_therapy_date DESC;
}}}  
== Прикажување на мобилен оператор на кој што се најчесто корисниците претплатници. Доколку мобилниот оператор **Telecom** започнува на 072 или 071, мобилниот оператор **VIP** започнува на 078 или 075.
{{{
   SELECT
    CASE
        WHEN SUBSTRING(u.number, 1, 3) IN ('072', '071') THEN 'Telecom'
        WHEN SUBSTRING(u.number, 1, 3) IN ('078', '075') THEN 'VIP'
        ELSE 'Other Operator'
    END AS mobile_operator,
    COUNT(u.id) AS number_of_users
FROM
    project.users u
where
    u.number LIKE '072%' OR
    u.number LIKE '071%' OR
    u.number LIKE '078%' OR
    u.number LIKE '075%'
GROUP BY
    mobile_operator
ORDER BY
    number_of_users DESC;
}}}
== Kолку пари се трошат за платите на вработените во секој ветеринарен центар.
{{{
   SELECT
    vc.name AS vet_center_name,
    SUM(CASE WHEN j.predictedSalery LIKE '%EUR%' THEN CAST(SUBSTRING(j.predictedSalery, 1, POSITION(' ' IN j.predictedSalery) - 1) AS DECIMAL) ELSE 0 END) AS total_salary_expense
FROM
    project.vet_centers vc
JOIN
    project.jobs j ON vc.id = j.vetCentersID
LEFT JOIN
    project.users u ON vc.id = u.jobs_id
GROUP BY
    vc.id, vet_center_name
ORDER BY
   total_salary_expense DESC;
}}}
== Прикажување на ветеринарни центри кои што корисникот може да ги посети доколку живее во Охрид.
{{{ 
SELECT
  vc.name AS vet_center_name,
  vc.description AS vet_center_description,
  vc.adress AS vet_center_address,
  vc.phoneNumber AS vet_center_phone_number
FROM
  project.vet_centers vc
JOIN
  project.cities c ON vc.citiesID = c.ID
WHERE
  c.name = 'Ohrid';
}}}
== Прикажување кој корисник има најголем број на миленичиња.
{{{
SELECT u.name, u.lastname, COUNT(p.ID) AS pet_count
FROM project.users u
JOIN project.pets p ON u.ID = p.usersID
GROUP BY u.ID
ORDER BY pet_count DESC
LIMIT 1;
}}}
== Прикажување која раса е најчесто застапена.
{{{
SELECT b.name AS breed_name, COUNT(p.ID) AS pet_count
FROM project.breeds b
JOIN project.type_of_pets t ON b.ID = t.ID
JOIN project.pets p ON t.ID = p.typeOfPetsID
GROUP BY b.ID
ORDER BY pet_count DESC
LIMIT 1;
}}}
== Прикажување која терапија за која дијагноза е препишена и од кој доктор е препишена.
{{{
 SELECT
    t.description AS therapy_description,
    d.description AS diagnosis_description,
    u.name AS doctor_name,
    u.lastname AS doctor_lastname
FROM
    project.therapy t
JOIN
    project.diagnostics d ON d.id = t.diagnosticsID
JOIN
    project.users u ON u.id = d.usersID
JOIN
    project.pets p ON p.id = d.usersID
ORDER BY
    t.id;
}}}
== Прикажување на возраста на секое милениче.
{{{
SELECT
  p.ID AS pet_id,
  p.color,
  p.description AS pet_description,
  p.dateOfBirthday,
  AGE(current_date, p.dateOfBirthday) AS pet_age
FROM
  project.pets p;
}}}
== Прикажување на држава која е најчест производител на лекови.
{{{
 select
	state as country,
	COUNT(*) as most_common_manufacturer
from
	project.manufacturers
group by
	state
order by
	most_common_manufacturer desc
limit 1;
}}}
== Прикажување на продукти кои што би можело да се потрошат најбрзо бидејќи има побарано најголема количина за нив.
{{{
SELECT
  p.name AS product_name,
  p.description AS product_description,
  AVG(o.quantity) AS avg_sales_quantity
FROM
  project.products p
JOIN
  project.product_are_made_orders po ON p.ID = po.id_products
JOIN
  project.orders o ON po.id_orders = o.ID
GROUP BY
  p.ID
ORDER BY
  avg_sales_quantity DESC;
}}}
== Прикажи го продуктот за кој постојат најмал број направени нарачки.
{{{

WITH product_orders_count AS (
    SELECT
        p.id AS product_id,
        p.name AS product_name,
        p.description AS product_description,
        p.price AS product_price,
        COALESCE(COUNT(o.id), 0) AS orders_count
    FROM
        project.products p
    LEFT JOIN
        project.product_are_made_orders pamo ON p.id = pamo.id_products
    LEFT JOIN
        project.orders o ON pamo.id_orders = o.id
    GROUP BY
        p.id, p.name, p.description, p.price
)
SELECT
    product_id,
    product_name,
    product_description,
    product_price,
    orders_count
FROM
    product_orders_count
WHERE
    orders_count = (
        SELECT MIN(orders_count)
        FROM product_orders_count
    );

}}}