user
33 lines
810 B
SQL
33 lines
810 B
SQL
CREATE VIEW sessions_statistics AS
|
|
SELECT
|
|
s.session_id,
|
|
s.begin_at,
|
|
s.ticket_price,
|
|
h.hall_id,
|
|
h.hall_name,
|
|
h.capacity AS total_seats,
|
|
m.movie_id,
|
|
m.title AS movie_title,
|
|
m.duration,
|
|
g.genre_id,
|
|
g.genre_name,
|
|
COUNT(t.ticket_id) AS sold_tickets,
|
|
COUNT(r.refund_id) AS refunded_tickets,
|
|
SUM(CASE WHEN t.ticket_id IS NOT NULL THEN s.ticket_price ELSE 0 END) AS sales_revenue,
|
|
SUM(CASE WHEN r.refund_id IS NOT NULL THEN r.refund_amount ELSE 0 END) AS refund_expenses
|
|
FROM halls h
|
|
JOIN sessions s
|
|
ON s.hall_id = h.hall_id
|
|
JOIN movies m
|
|
ON s.movie_id = m.movie_id
|
|
JOIN genres g
|
|
ON m.genre_id = g.genre_id
|
|
LEFT JOIN tickets t
|
|
ON t.session_id = s.session_id
|
|
LEFT JOIN refunds r
|
|
ON r.ticket_id = t.ticket_id
|
|
GROUP BY
|
|
s.session_id;
|
|
|
|
|