| 335 | | === Годишен извештај за нови корисници и резервации по месец |
| 336 | | Агрегира број на нови корисници и број на резервации месечно за целата година. Дава целосен увид во растот на платформата. |
| 337 | | {{{ |
| 338 | | CREATE OR REPLACE FUNCTION GetMonthlyUserReservationReport(year INT) |
| 339 | | RETURNS TABLE( |
| 340 | | Month INT, |
| 341 | | NewUsers bigint, |
| 342 | | TotalReservations bigint |
| 343 | | ) |
| 344 | | LANGUAGE plpgsql |
| 345 | | AS $$ |
| 346 | | BEGIN |
| 347 | | RETURN QUERY |
| 348 | | SELECT |
| 349 | | m.Month, |
| 350 | | COUNT(DISTINCT u.id_user) AS NewUsers, |
| 351 | | COALESCE(SUM(r.ReservationCount),0) AS TotalReservations |
| 352 | | FROM ( |
| 353 | | SELECT EXTRACT(MONTH FROM birth_date)::INT AS Month, id_user |
| 354 | | FROM travel_sage.users |
| 355 | | WHERE EXTRACT(YEAR FROM birth_date) = year |
| 356 | | ) u |
| 357 | | LEFT JOIN ( |
| 358 | | SELECT EXTRACT(MONTH FROM time_point)::INT AS Month, id_reservation, |
| 359 | | id_user, COUNT(*) AS ReservationCount |
| 360 | | FROM travel_sage.reservation |
| 361 | | WHERE EXTRACT(YEAR FROM time_point) = year |
| 362 | | GROUP BY Month, id_reservation, id_user |
| 363 | | ) r ON u.id_user = r.id_user AND u.Month = r.Month |
| 364 | | RIGHT JOIN (SELECT generate_series(1,12) AS Month) m ON m.Month = u.Month OR m.Month = r.Month |
| 365 | | GROUP BY m.Month |
| 366 | | ORDER BY m.Month; |
| 367 | | END; |
| 368 | | $$; |
| 369 | | }}} |
