Напредни извештаи од базата (SQL, складирани процедури и релациона алгебра)

1. Месечни приходи и перформанси на плаќања

По месец прикажува колку плаќања има, колку се успешни/неуспешни, колку е приходот од термини наспроти приходи од купување пакет, и месечна промена на приход.

SQL

WITH bounds AS (
  SELECT
    date_trunc('month', MIN(p."timestamp"))::date AS min_month,
    date_trunc('month', MAX(p."timestamp"))::date AS max_month
  FROM payment p
),
months AS (
  SELECT generate_series(min_month, max_month, interval '1 month')::date AS month_start
  FROM bounds
),
pay AS (
  SELECT
    date_trunc('month', p."timestamp")::date AS month_start,
    p.status,
    p.amount,
    p.appointment_id,
    p.package_purchase_id
  FROM payment p
),
agg AS (
  SELECT
    m.month_start,
    COUNT(pay.*) AS payment_cnt,
    COUNT(*) FILTER (WHERE pay.status = 'PAID') AS paid_cnt,
    COUNT(*) FILTER (WHERE pay.status = 'PENDING') AS pending_cnt,
    COUNT(*) FILTER (WHERE pay.status = 'FAILED') AS failed_cnt,
    COUNT(*) FILTER (WHERE pay.status = 'REFUNDED') AS refunded_cnt,
    COALESCE(SUM(pay.amount) FILTER (WHERE pay.status = 'PAID'), 0)::numeric(12,2) AS paid_amount_total,
    COALESCE(SUM(pay.amount) FILTER (WHERE pay.status = 'REFUNDED'), 0)::numeric(12,2) AS refunded_amount_total,
    COALESCE(SUM(pay.amount) FILTER (WHERE pay.status = 'PAID' AND pay.appointment_id IS NOT NULL), 0)::numeric(12,2) AS paid_appointment_amount,
    COALESCE(SUM(pay.amount) FILTER (WHERE pay.status = 'PAID' AND pay.appointment_id IS NULL AND pay.package_purchase_id IS NOT NULL), 0)::numeric(12,2) AS paid_package_amount
  FROM months m
  LEFT JOIN pay ON pay.month_start = m.month_start
  GROUP BY m.month_start
)
SELECT
  a.month_start,
  a.payment_cnt,
  a.paid_cnt,
  a.pending_cnt,
  a.failed_cnt,
  a.refunded_cnt,
  a.paid_amount_total,
  a.refunded_amount_total,
  a.paid_appointment_amount,
  a.paid_package_amount,
  ROUND(100.0 * a.paid_cnt / NULLIF(a.payment_cnt, 0), 2) AS paid_rate_pct,
  ROUND(100.0 * a.failed_cnt / NULLIF(a.payment_cnt, 0), 2) AS failed_rate_pct,
  ROUND(
    a.paid_amount_total
    - COALESCE(LAG(a.paid_amount_total) OVER (ORDER BY a.month_start), 0),
    2
  ) AS mom_paid_amount_change
FROM agg a
ORDER BY a.month_start;

Релациона алгебра

B ← γ_{min_m:=min(month(ts)), max_m:=max(month(ts))}(Payment)
M ← generate_series(B.min_m, B.max_m, 1 month)
P ← π_{month:=month(ts), status, amount, appointment_id, package_purchase_id}(Payment)
R ← γ_{month; cnt:=count(*), paid:=count_{status='PAID'}(*), pending:=count_{status='PENDING'}(*), failed:=count_{status='FAILED'}(*), refunded:=count_{status='REFUNDED'}(*), paid_sum:=sum_{status='PAID'}(amount), paid_appt:=sum_{status='PAID'∧appointment_id≠NULL}(amount), paid_pkg:=sum_{status='PAID'∧appointment_id=NULL∧package_purchase_id≠NULL}(amount)}(M ⟕_{M.month=P.month} P)

Овој извештај веднаш покажува дали растот доаѓа од класични термини или од продажба на пакети и дали има пад на success rate по месеци.

2. Најпопуларни услуги според закажувања, приход и оценки

За секоја услуга покажува број на закажувања по статус, алоциран платен приход, просечна оценка и број рецензии, со ранг на популарност.

SQL

WITH appointment_base AS (
  SELECT
    a.appointment_id,
    UPPER(s.name) AS status_name
  FROM appointment a
  JOIN status s ON s.status_id = a.status_id
),
service_lines AS (
  SELECT
    aps.appointment_id,
    aps.service_id,
    sv.name AS service_name,
    c.name AS category_name,
    sv.price AS service_list_price
  FROM appointmentservice aps
  JOIN service sv ON sv.service_id = aps.service_id
  LEFT JOIN category c ON c.category_id = sv.category_id
),
appointment_list_totals AS (
  SELECT
    sl.appointment_id,
    SUM(sl.service_list_price) AS list_total_price
  FROM service_lines sl
  GROUP BY sl.appointment_id
),
paid_amount_per_appt AS (
  SELECT
    p.appointment_id,
    MAX(p.amount) AS paid_amount
  FROM payment p
  WHERE p.status = 'PAID'
    AND p.appointment_id IS NOT NULL
  GROUP BY p.appointment_id
),
revenue_alloc AS (
  SELECT
    sl.appointment_id,
    sl.service_id,
    (pa.paid_amount * sl.service_list_price / NULLIF(alt.list_total_price, 0))::numeric(12,2) AS allocated_amount
  FROM service_lines sl
  JOIN appointment_list_totals alt ON alt.appointment_id = sl.appointment_id
  JOIN paid_amount_per_appt pa ON pa.appointment_id = sl.appointment_id
),
reviews_by_service AS (
  SELECT
    aps.service_id,
    ROUND(AVG(r.rating)::numeric, 2) AS avg_rating,
    COUNT(r.review_id) AS review_count
  FROM appointmentservice aps
  JOIN payment p
    ON p.appointment_id = aps.appointment_id
   AND p.status = 'PAID'
  JOIN review r ON r.payment_id = p.payment_id
  GROUP BY aps.service_id
),
service_stats AS (
  SELECT
    sl.service_id,
    sl.service_name,
    sl.category_name,
    COUNT(*) AS bookings_total,
    COUNT(*) FILTER (WHERE ab.status_name = 'COMPLETED') AS completed_bookings,
    COUNT(*) FILTER (WHERE ab.status_name = 'SCHEDULED') AS scheduled_bookings,
    COUNT(*) FILTER (WHERE ab.status_name = 'CANCELLED') AS cancelled_bookings,
    COUNT(*) FILTER (WHERE ab.status_name = 'NO_SHOW') AS no_show_bookings,
    COALESCE(SUM(ra.allocated_amount), 0)::numeric(12,2) AS allocated_paid_revenue
  FROM service_lines sl
  JOIN appointment_base ab ON ab.appointment_id = sl.appointment_id
  LEFT JOIN revenue_alloc ra
    ON ra.appointment_id = sl.appointment_id
   AND ra.service_id = sl.service_id
  GROUP BY sl.service_id, sl.service_name, sl.category_name
)
SELECT
  ss.service_id,
  ss.service_name,
  ss.category_name,
  ss.bookings_total,
  ss.completed_bookings,
  ss.scheduled_bookings,
  ss.cancelled_bookings,
  ss.no_show_bookings,
  ss.allocated_paid_revenue,
  COALESCE(rv.avg_rating, 0)::numeric(4,2) AS avg_rating,
  COALESCE(rv.review_count, 0) AS review_count,
  DENSE_RANK() OVER (
    ORDER BY ss.completed_bookings DESC, ss.allocated_paid_revenue DESC
  ) AS popularity_rank
FROM service_stats ss
LEFT JOIN reviews_by_service rv ON rv.service_id = ss.service_id
ORDER BY popularity_rank, ss.service_id;

Релациона алгебра

SL ← AppointmentService ⨝ Service ⨝ Category
AB ← Appointment ⨝ Status
T ← γ_{appointment_id; list_total:=Σ(service_price)}(SL)
PP ← σ_{status='PAID' ∧ appointment_id≠NULL}(Payment)
RA ← γ_{appointment_id, service_id; alloc:=paid_amount*service_price/list_total}(SL ⨝ T ⨝ PP)
RV ← γ_{service_id; avg_rating:=AVG(rating), review_count:=COUNT(*)}(AppointmentService ⨝ PP ⨝ Review)
S ← γ_{service_id, service_name, category_name; bookings:=COUNT(*), completed:=COUNT_{status='COMPLETED'}(*), revenue:=Σ(alloc)}(SL ⨝ AB ⟕ RA)
Result ← S ⟕ RV

Ова е најкорисен top services извештај за одлуки, кои услуги се чести, кои носат реален приход и како се оценети од клиенти.

3. Лојалност и активност на клиенти

По корисник дава извештај за активност со термини, платени/неуспешни плаќања, купени пакети, искористеност и моментални loyalty поени.

SQL

WITH appt_agg AS (
  SELECT
    a.user_id,
    COUNT(*) AS appointments_total,
    COUNT(*) FILTER (WHERE UPPER(s.name) = 'SCHEDULED') AS scheduled_cnt,
    COUNT(*) FILTER (WHERE UPPER(s.name) = 'COMPLETED') AS completed_cnt,
    COUNT(*) FILTER (WHERE UPPER(s.name) = 'CANCELLED') AS cancelled_cnt,
    COUNT(*) FILTER (WHERE UPPER(s.name) = 'NO_SHOW') AS no_show_cnt,
    MAX(a.appointment_time) AS last_appointment_at
  FROM appointment a
  JOIN status s ON s.status_id = a.status_id
  GROUP BY a.user_id
),
pay_user_map AS (
  SELECT
    p.payment_id,
    COALESCE(a.user_id, upp.user_id) AS user_id,
    p.status,
    p.amount,
    p.points_used,
    p.appointment_id,
    p.package_purchase_id,
    p."timestamp"
  FROM payment p
  LEFT JOIN appointment a ON a.appointment_id = p.appointment_id
  LEFT JOIN userpackagepurchase upp ON upp.purchase_id = p.package_purchase_id
),
pay_agg AS (
  SELECT
    pum.user_id,
    COUNT(*) AS payments_total,
    COUNT(*) FILTER (WHERE pum.status = 'PAID') AS paid_payments_cnt,
    COUNT(*) FILTER (WHERE pum.status = 'FAILED') AS failed_payments_cnt,
    COUNT(*) FILTER (WHERE pum.status = 'REFUNDED') AS refunded_payments_cnt,
    COALESCE(SUM(pum.amount) FILTER (WHERE pum.status = 'PAID'), 0)::numeric(12,2) AS paid_amount_total,
    COALESCE(SUM(pum.amount) FILTER (WHERE pum.status = 'PAID' AND pum.appointment_id IS NOT NULL), 0)::numeric(12,2) AS paid_appointment_amount,
    COALESCE(SUM(pum.amount) FILTER (WHERE pum.status = 'PAID' AND pum.appointment_id IS NULL AND pum.package_purchase_id IS NOT NULL), 0)::numeric(12,2) AS paid_package_amount,
    COALESCE(SUM(pum.points_used) FILTER (WHERE pum.status = 'PAID'), 0) AS points_used_total,
    MAX(pum."timestamp") AS last_payment_at
  FROM pay_user_map pum
  WHERE pum.user_id IS NOT NULL
  GROUP BY pum.user_id
),
pkg_agg AS (
  SELECT
    upp.user_id,
    COUNT(*) AS package_purchases_total,
    COUNT(*) FILTER (WHERE upp.status = 'ACTIVE') AS active_packages_cnt,
    COALESCE(SUM(upp.total_uses), 0) AS total_package_uses_bought,
    COALESCE(SUM(upp.remaining_uses), 0) AS total_package_uses_remaining,
    COALESCE(SUM(upp.total_uses - upp.remaining_uses), 0) AS total_package_uses_used,
    MAX(upp.purchased_at) AS last_package_purchase_at
  FROM userpackagepurchase upp
  GROUP BY upp.user_id
)
SELECT
  u.user_id,
  u.full_name,
  u.email,
  COALESCE(lc.points, 0) AS loyalty_points_balance,
  COALESCE(a.appointments_total, 0) AS appointments_total,
  COALESCE(a.completed_cnt, 0) AS completed_cnt,
  COALESCE(a.scheduled_cnt, 0) AS scheduled_cnt,
  COALESCE(a.cancelled_cnt, 0) AS cancelled_cnt,
  COALESCE(a.no_show_cnt, 0) AS no_show_cnt,
  ROUND(100.0 * COALESCE(a.completed_cnt, 0) / NULLIF(COALESCE(a.appointments_total, 0), 0), 2) AS completion_rate_pct,
  COALESCE(p.payments_total, 0) AS payments_total,
  COALESCE(p.paid_payments_cnt, 0) AS paid_payments_cnt,
  COALESCE(p.failed_payments_cnt, 0) AS failed_payments_cnt,
  COALESCE(p.refunded_payments_cnt, 0) AS refunded_payments_cnt,
  COALESCE(p.paid_amount_total, 0)::numeric(12,2) AS paid_amount_total,
  COALESCE(p.paid_appointment_amount, 0)::numeric(12,2) AS paid_appointment_amount,
  COALESCE(p.paid_package_amount, 0)::numeric(12,2) AS paid_package_amount,
  COALESCE(p.points_used_total, 0) AS points_used_total,
  COALESCE(pk.package_purchases_total, 0) AS package_purchases_total,
  COALESCE(pk.active_packages_cnt, 0) AS active_packages_cnt,
  COALESCE(pk.total_package_uses_bought, 0) AS total_package_uses_bought,
  COALESCE(pk.total_package_uses_used, 0) AS total_package_uses_used,
  COALESCE(pk.total_package_uses_remaining, 0) AS total_package_uses_remaining,
  ROW_NUMBER() OVER (
    ORDER BY COALESCE(p.paid_amount_total, 0) DESC, COALESCE(lc.points, 0) DESC, u.user_id
  ) AS value_rank
FROM "User" u
LEFT JOIN loyaltycard lc ON lc.user_id = u.user_id
LEFT JOIN appt_agg a ON a.user_id = u.user_id
LEFT JOIN pay_agg p ON p.user_id = u.user_id
LEFT JOIN pkg_agg pk ON pk.user_id = u.user_id
ORDER BY value_rank;

Релациона алгебра

A ← γ_{user_id; appt_total:=COUNT(*), completed:=COUNT_{status='COMPLETED'}(*), ...}(Appointment ⨝ Status)
P0 ← Payment ⟕ Appointment ⟕ UserPackagePurchase
P ← γ_{user_id; paid_sum:=Σ_{status='PAID'}(amount), paid_cnt:=COUNT_{status='PAID'}(*), ...}(P0)
K ← γ_{user_id; pkg_cnt:=COUNT(*), active_pkg:=COUNT_{status='ACTIVE'}(*), uses_bought:=Σ(total_uses), uses_rem:=Σ(remaining_uses)}(UserPackagePurchase)
Result ← User ⟕ LoyaltyCard ⟕ A ⟕ P ⟕ K

Извештајот е добар за CRM сегментација: брзо се гледа кои клиенти носат најмногу приход, колку се активни и каков им е loyalty балансот.

4. Продажба и искористеност на пакети

За секој пакет покажува колку е продаден, колку е исплатен, колку користења се потрошени/останати и колку е „популарен“ во реална употреба.

SQL

WITH package_base AS (
  SELECT
    p.package_id,
    p.name AS package_name,
    p.max_usage,
    p.total_price,
    COUNT(ps.service_id) AS services_in_package,
    COALESCE(SUM(ps.discounted_price), 0)::numeric(12,2) AS computed_services_price_sum
  FROM package p
  LEFT JOIN packageservice ps ON ps.package_id = p.package_id
  GROUP BY p.package_id, p.name, p.max_usage, p.total_price
),
purchase_stats AS (
  SELECT
    upp.package_id,
    COUNT(*) AS purchases_cnt,
    COUNT(*) FILTER (WHERE upp.status = 'ACTIVE') AS active_purchases_cnt,
    COUNT(*) FILTER (WHERE upp.status = 'EXPIRED') AS expired_purchases_cnt,
    COUNT(*) FILTER (WHERE upp.status = 'CANCELLED') AS cancelled_purchases_cnt,
    COALESCE(SUM(upp.total_uses), 0) AS total_uses_sold,
    COALESCE(SUM(upp.remaining_uses), 0) AS total_uses_remaining,
    COALESCE(SUM(upp.total_uses - upp.remaining_uses), 0) AS total_uses_used
  FROM userpackagepurchase upp
  GROUP BY upp.package_id
),
package_payment_stats AS (
  SELECT
    upp.package_id,
    COUNT(*) FILTER (WHERE p.status = 'PAID') AS paid_payments_cnt,
    COUNT(*) FILTER (WHERE p.status = 'PENDING') AS pending_payments_cnt,
    COUNT(*) FILTER (WHERE p.status = 'FAILED') AS failed_payments_cnt,
    COUNT(*) FILTER (WHERE p.status = 'REFUNDED') AS refunded_payments_cnt,
    COALESCE(SUM(p.amount) FILTER (WHERE p.status = 'PAID'), 0)::numeric(12,2) AS paid_amount_total
  FROM payment p
  JOIN userpackagepurchase upp ON upp.purchase_id = p.package_purchase_id
  WHERE p.appointment_id IS NULL
  GROUP BY upp.package_id
),
usage_ops AS (
  SELECT
    upp.package_id,
    COUNT(apu.*) AS linked_appointment_usages,
    COUNT(*) FILTER (WHERE apu.finalized_at IS NOT NULL) AS finalized_usages
  FROM userpackagepurchase upp
  LEFT JOIN appointmentpackageusage apu ON apu.purchase_id = upp.purchase_id
  GROUP BY upp.package_id
)
SELECT
  pb.package_id,
  pb.package_name,
  pb.max_usage,
  pb.total_price,
  pb.services_in_package,
  pb.computed_services_price_sum,
  COALESCE(ps.purchases_cnt, 0) AS purchases_cnt,
  COALESCE(ps.active_purchases_cnt, 0) AS active_purchases_cnt,
  COALESCE(ps.expired_purchases_cnt, 0) AS expired_purchases_cnt,
  COALESCE(ps.cancelled_purchases_cnt, 0) AS cancelled_purchases_cnt,
  COALESCE(ps.total_uses_sold, 0) AS total_uses_sold,
  COALESCE(ps.total_uses_used, 0) AS total_uses_used,
  COALESCE(ps.total_uses_remaining, 0) AS total_uses_remaining,
  ROUND(100.0 * COALESCE(ps.total_uses_used, 0) / NULLIF(COALESCE(ps.total_uses_sold, 0), 0), 2) AS usage_rate_pct,
  COALESCE(pps.paid_payments_cnt, 0) AS paid_payments_cnt,
  COALESCE(pps.pending_payments_cnt, 0) AS pending_payments_cnt,
  COALESCE(pps.failed_payments_cnt, 0) AS failed_payments_cnt,
  COALESCE(pps.refunded_payments_cnt, 0) AS refunded_payments_cnt,
  COALESCE(pps.paid_amount_total, 0)::numeric(12,2) AS paid_amount_total,
  ROUND(COALESCE(pps.paid_amount_total, 0) / NULLIF(COALESCE(ps.purchases_cnt, 0), 0), 2) AS avg_paid_per_purchase,
  COALESCE(uo.linked_appointment_usages, 0) AS linked_appointment_usages,
  COALESCE(uo.finalized_usages, 0) AS finalized_usages,
  DENSE_RANK() OVER (
    ORDER BY COALESCE(ps.purchases_cnt, 0) DESC, COALESCE(pps.paid_amount_total, 0) DESC
  ) AS package_popularity_rank
FROM package_base pb
LEFT JOIN purchase_stats ps ON ps.package_id = pb.package_id
LEFT JOIN package_payment_stats pps ON pps.package_id = pb.package_id
LEFT JOIN usage_ops uo ON uo.package_id = pb.package_id
ORDER BY package_popularity_rank, pb.package_id;

Релациона алгебра

PB ← γ_{package_id, name; svc_cnt:=COUNT(service_id), svc_sum:=Σ(discounted_price)}(Package ⟕ PackageService)
PS ← γ_{package_id; purchases:=COUNT(*), active:=COUNT_{status='ACTIVE'}(*), uses_sold:=Σ(total_uses), uses_used:=Σ(total_uses-remaining_uses)}(UserPackagePurchase)
PP ← γ_{package_id; paid_cnt:=COUNT_{status='PAID'}(*), paid_sum:=Σ_{status='PAID'}(amount)}(σ_{appointment_id=NULL}(Payment ⨝ UserPackagePurchase))
UO ← γ_{package_id; linked:=COUNT(*), finalized:=COUNT_{finalized_at≠NULL}(*)}(UserPackagePurchase ⟕ AppointmentPackageUsage)
Result ← PB ⟕ PS ⟕ PP ⟕ UO

Се гледа кои пакети навистина се продаваат и трошат, дали остануваат неискористени, и дали цената на пакет носи очекуван приход.