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

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_reports 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_reports 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
)

SitterReports <-
γ
  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) SitterReports 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

WITH params AS (
    SELECT 
        CAST(:start_date AS DATE) AS start_date, 
        CAST(:end_date AS DATE)   AS end_date
),

owner_reports 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_reports 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)}

OwnerReports <-
γ
  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) OwnerReports 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)
)