Changes between Initial Version and Version 1 of AdvancedReports

Timestamp:

05/22/26 01:18:14 (4 days ago)

Author:

181201

Comment:

--

AdvancedReports

@@ +1 @@
+= Complex DB Reports (SQL, Stored Procedures, Relational Algebra)
+
+== Sitter Performance
+This SQL query wants to evaluate and rank pet sitters to find the top performers. A top performer is a sitter who completes bookings in a reliable way, has high ratings and generates high revenue.
+This SQL query:
+
+* Analyzes the bookings by counting total, completed and missed bookings
+* Calculates total money generated from completed bookings only
+* Evaluates quality by finding average customer ratings from reviews table
+* Ranks the sitters using a custom weighted performance score - valuing money, completed bookings and high ratings.
+
+=== SQL
+{{{#!sql
+WITH params AS (
+    SELECT 
+        CAST(:start_date AS DATE) AS start_date, 
+        CAST(:end_date AS DATE)   AS end_date
+),
+
+sitter_stats AS (
+    SELECT 
+        b.sitter_id,
+        COUNT(b.booking_id) AS total_bookings,
+        COUNT(b.booking_id) FILTER (WHERE b.status = 'Completed') AS completed_bookings,
+        COUNT(b.booking_id) FILTER (WHERE b.status IN ('Canceled', 'Rejected')) AS missed_bookings
+    FROM bookings b
+    JOIN params p ON b.date_from >= p.start_date AND b.date_from < p.end_date
+    GROUP BY b.sitter_id
+),
+
+sitter_financials AS (
+    SELECT 
+        b.sitter_id,
+        SUM(pay.amount) AS total_revenue
+    FROM bookings b
+    JOIN payments pay ON b.booking_id = pay.booking_id
+    JOIN params p ON b.date_from >= p.start_date AND b.date_from < p.end_date
+    WHERE b.status = 'Completed'
+    GROUP BY b.sitter_id
+),
+
+sitter_ratings AS (
+    SELECT 
+        b.sitter_id,
+        AVG(r.rating)::numeric(10,2) AS avg_rating,
+        COUNT(r.review_id) AS total_reviews
+    FROM bookings b
+    JOIN reviews r ON b.booking_id = r.booking_id
+    JOIN params p ON b.date_from >= p.start_date AND b.date_from < p.end_date
+    GROUP BY b.sitter_id
+)
+
+SELECT 
+    u.user_id, 
+    u.username, 
+    u.first_name, 
+    u.last_name,
+    COALESCE(ss.total_bookings, 0) AS total_bookings,
+    COALESCE(ss.completed_bookings, 0) AS completed_bookings,
+    COALESCE(ss.missed_bookings, 0) AS missed_bookings,
+    COALESCE(sr.avg_rating, 0) AS avg_rating,
+    COALESCE(sr.total_reviews, 0) AS total_reviews,
+    COALESCE(sf.total_revenue, 0) AS total_revenue,
+    
+    (
+        COALESCE(sf.total_revenue, 0) * 0.5 
+        + COALESCE(ss.completed_bookings, 0) * 10 
+        + COALESCE(sr.avg_rating, 0) * 15 
+        - COALESCE(ss.missed_bookings, 0) * 5
+    ) AS sitter_score,
+    
+    DENSE_RANK() OVER (
+        ORDER BY (
+            COALESCE(sf.total_revenue, 0) * 0.5 
+            + COALESCE(ss.completed_bookings, 0) * 10 
+            + COALESCE(sr.avg_rating, 0) * 15 
+            - COALESCE(ss.missed_bookings, 0) * 5
+        ) DESC, 
+        COALESCE(sr.avg_rating, 0) DESC
+    ) AS sitter_rank
+
+FROM users u
+JOIN pet_sitters ps ON u.user_id = ps.user_id
+LEFT JOIN sitter_stats ss ON ss.sitter_id = ps.user_id
+LEFT JOIN sitter_financials sf ON sf.sitter_id = ps.user_id
+LEFT JOIN sitter_ratings sr ON sr.sitter_id = ps.user_id
+WHERE COALESCE(ss.total_bookings, 0) > 0
+ORDER BY sitter_rank
+LIMIT 10;
+}}}
+
+=== Relational algebra
+{{{
+Params <- {(start_date, end_date)}
+
+SitterStats <-
+γ
+  sitter_id := b.sitter_id;
+  total_bookings := COUNT(b.booking_id);
+  completed_bookings := COUNT(b.status = 'Completed');
+  missed_bookings := COUNT(b.status = 'Canceled' ∨ b.status = 'Rejected')
+(
+  bookings b ⨝
+  (b.date_from ≥ p.start_date ∧ b.date_from < p.end_date)
+  Params p
+)
+
+SitterFinancials <-
+γ
+  sitter_id := b.sitter_id;
+  total_revenue := SUM(pay.amount)
+(
+  σ b.status = 'Completed'
+  (
+    (bookings b ⨝ (b.booking_id = pay.booking_id) payments pay)
+    ⨝ (b.date_from ≥ p.start_date ∧ b.date_from < p.end_date) Params p
+  )
+)
+
+SitterRatings <-
+γ
+  sitter_id := b.sitter_id;
+  avg_rating := AVG(r.rating);
+  total_reviews := COUNT(r.review_id)
+(
+  (bookings b ⨝ (b.booking_id = r.booking_id) reviews r)
+  ⨝ (b.date_from ≥ p.start_date ∧ b.date_from < p.end_date) Params p
+)
+
+SitterBase <-
+(users u ⨝ (u.user_id = ps.user_id) pet_sitters ps)
+
+MergedData <-
+(
+  (
+    (SitterBase ⟕ (u.user_id = ss.sitter_id) SitterStats ss)
+    ⟕ (u.user_id = sf.sitter_id) SitterFinancials sf
+  )
+  ⟕ (u.user_id = sr.sitter_id) SitterRatings sr
+)
+
+ActiveSitters <-
+σ COALESCE(ss.total_bookings, 0) > 0
+(MergedData)
+
+ScoredSitters <-
+π
+  user_id, username, first_name, last_name,
+  total_bookings := COALESCE(ss.total_bookings, 0),
+  completed_bookings := COALESCE(ss.completed_bookings, 0),
+  missed_bookings := COALESCE(ss.missed_bookings, 0),
+  avg_rating := COALESCE(sr.avg_rating, 0),
+  total_reviews := COALESCE(sr.total_reviews, 0),
+  total_revenue := COALESCE(sf.total_revenue, 0),
+  sitter_score := (
+    COALESCE(sf.total_revenue, 0) * 0.5 + 
+    COALESCE(ss.completed_bookings, 0) * 10 + 
+    COALESCE(sr.avg_rating, 0) * 15 - 
+    COALESCE(ss.missed_bookings, 0) * 5
+  )
+(ActiveSitters)
+
+RankedSitters <-
+rank_dense
+  sitter_rank :=
+    ORDER BY
+      sitter_score DESC,
+      avg_rating DESC
+(ScoredSitters)
+
+Result <-
+topK_{K := 10}
+(
+  τ sitter_rank ASC
+  (RankedSitters)
+)
+}}}
+
+== Highest paying customers
+This SQL query wants to find the platform's most profitable pet owners and discover their primary service interests.
+This query:
+
+* Sums total profit generated from successful bookings
+* Counts canceled bookings to determine customer reliability
+* Discovers the user's top interest by ranking their most frequently booked service types
+* Counts the total number of registered pets per owner
+* Combines everything and ranks customers based on total profit and successful booking volume
+
+=== SQL
+{{{#!sql
+WITH params AS (
+    SELECT 
+        CAST('2020-01-01' AS DATE) AS start_date, 
+        CAST('2030-01-01' AS DATE) AS end_date
+),
+
+owner_financials AS (
+    SELECT 
+        b.owner_id,
+        SUM(pay.amount) AS total_profit_generated,
+        COUNT(DISTINCT b.booking_id) AS successful_bookings
+    FROM bookings b
+    JOIN payments pay ON b.booking_id = pay.booking_id
+    JOIN params p ON b.date_from >= p.start_date AND b.date_from < p.end_date
+    WHERE b.status = 'Completed'
+    GROUP BY b.owner_id
+),
+
+owner_activity AS (
+    SELECT 
+        b.owner_id,
+        COUNT(b.booking_id) FILTER (WHERE b.status IN ('Canceled', 'Rejected')) AS canceled_bookings
+    FROM bookings b
+    JOIN params p ON b.date_from >= p.start_date AND b.date_from < p.end_date
+    GROUP BY b.owner_id
+),
+
+service_counts AS (
+    SELECT 
+        b.owner_id,
+        s.type AS service_type,
+        COUNT(bs.service_id) AS times_booked,
+        ROW_NUMBER() OVER(PARTITION BY b.owner_id ORDER BY COUNT(bs.service_id) DESC) as rank_num
+    FROM bookings b
+    JOIN booking_services bs ON b.booking_id = bs.booking_id
+    JOIN services s ON bs.service_id = s.service_id
+    JOIN params p ON b.date_from >= p.start_date AND b.date_from < p.end_date
+    GROUP BY b.owner_id, s.type
+),
+
+favorite_service AS (
+    SELECT 
+        owner_id, 
+        service_type AS top_interest
+    FROM service_counts
+    WHERE rank_num = 1
+),
+
+pet_portfolio AS (
+    SELECT 
+        owner_id,
+        COUNT(pet_id) AS registered_pets
+    FROM pets
+    GROUP BY owner_id
+)
+
+SELECT 
+    u.user_id, 
+    u.first_name, 
+    u.last_name,
+    COALESCE(ofin.successful_bookings, 0) AS successful_bookings,
+    COALESCE(oa.canceled_bookings, 0) AS canceled_bookings,
+    COALESCE(pp.registered_pets, 0) AS total_pets,
+    COALESCE(fs.top_interest, 'Unknown') AS top_interest,
+    COALESCE(ofin.total_profit_generated, 0) AS total_profit_generated,
+    
+    DENSE_RANK() OVER (
+        ORDER BY 
+            COALESCE(ofin.total_profit_generated, 0) DESC, 
+            COALESCE(ofin.successful_bookings, 0) DESC
+    ) AS customer_rank
+
+FROM users u
+JOIN pet_owners po ON u.user_id = po.user_id
+LEFT JOIN owner_financials ofin ON po.user_id = ofin.owner_id
+LEFT JOIN owner_activity oa ON po.user_id = oa.owner_id
+LEFT JOIN favorite_service fs ON po.user_id = fs.owner_id
+LEFT JOIN pet_portfolio pp ON po.user_id = pp.owner_id
+WHERE COALESCE(ofin.successful_bookings, 0) > 0
+ORDER BY customer_rank
+LIMIT 10;
+}}}
+
+=== Relational algebra
+{{{
+Params <- {(start_date, end_date)}
+
+OwnerFinancials <-
+γ
+  owner_id := b.owner_id;
+  total_profit := SUM(pay.amount);
+  successful := COUNT_DISTINCT(b.booking_id)
+(
+  σ b.status = 'Completed'
+  (
+    (bookings b ⨝ (b.booking_id = pay.booking_id) payments pay)
+    ⨝ (b.date_from ≥ p.start_date ∧ b.date_from < p.end_date) Params p
+  )
+)
+
+OwnerActivity <-
+γ
+  owner_id := b.owner_id;
+  canceled := COUNT(b.status = 'Canceled' ∨ b.status = 'Rejected')
+(
+  bookings b ⨝ (b.date_from ≥ p.start_date ∧ b.date_from < p.end_date) Params p
+)
+
+ServiceCounts <-
+γ
+  owner_id := b.owner_id;
+  service_type := s.type;
+  times_booked := COUNT(bs.service_id)
+(
+  (
+    (bookings b ⨝ (b.booking_id = bs.booking_id) booking_services bs)
+    ⨝ (bs.service_id = s.service_id) services s
+  )
+  ⨝ (b.date_from ≥ p.start_date ∧ b.date_from < p.end_date) Params p
+)
+
+RankedServices <-
+rank_row_number
+  rank_num :=
+    ORDER BY times_booked DESC
+    PARTITION BY owner_id
+(ServiceCounts)
+
+FavoriteService <-
+π
+  owner_id,
+  top_interest := service_type
+(
+  σ rank_num = 1 (RankedServices)
+)
+
+PetPortfolio <-
+γ
+  owner_id := owner_id;
+  registered_pets := COUNT(pet_id)
+(pets)
+
+OwnerBase <-
+(users u ⨝ (u.user_id = po.user_id) pet_owners po)
+
+MergedData <-
+(
+  (
+    (
+      (OwnerBase ⟕ (u.user_id = ofin.owner_id) OwnerFinancials ofin)
+      ⟕ (u.user_id = oa.owner_id) OwnerActivity oa
+    )
+    ⟕ (u.user_id = fs.owner_id) FavoriteService fs
+  )
+  ⟕ (u.user_id = pp.owner_id) PetPortfolio pp
+)
+
+ActiveOwners <-
+σ COALESCE(ofin.successful, 0) > 0
+(MergedData)
+
+ScoredOwners <-
+π
+  user_id, first_name, last_name,
+  successful_bookings := COALESCE(ofin.successful, 0),
+  canceled_bookings := COALESCE(oa.canceled, 0),
+  total_pets := COALESCE(pp.registered_pets, 0),
+  top_interest := COALESCE(fs.top_interest, 'Unknown'),
+  total_profit_generated := COALESCE(ofin.total_profit, 0)
+(ActiveOwners)
+
+RankedOwners <-
+rank_dense
+  customer_rank :=
+    ORDER BY total_profit_generated DESC, successful_bookings DESC
+(ScoredOwners)
+
+Result <-
+topK_{K := 10}
+(
+  τ customer_rank ASC
+  (RankedOwners)
+)
+}}}