Ticket Sales Analysis

-- Explore the data in the table
SELECT *
FROM public.sales
LIMIT 20;

--How much were commissions earned from ticket sales grouped by event. The top 15 events with the highest earned commissions should be shown.
SELECT
	s.eventid,
	e.eventname,
	v.venuename,
	SUM(s.pricepaid) AS total_ticket_price,
	SUM(s.commission) AS total_commissions
FROM
	public.sales AS s
LEFT JOIN 
	public.event AS e
ON
	s.eventid = e.eventid
LEFT JOIN 
	public.venue AS v
ON
	e.venueid = v.venueid
GROUP BY
	s.eventid,
	e.eventname,
	v.venuename
ORDER BY
	total_commissions DESC
LIMIT 15;

--examining ticket sales per event in New York state for the month of January 2008.
SELECT 
	e.eventname,
	v.venuename,
	SUM(s.pricepaid) AS total_sales,
	SUM(s.commission) AS commissions_earned
FROM
	public.sales as s
INNER JOIN 
	public.event as e
ON 
	s.eventid = e.eventid
INNER JOIN
	public.venue as v
ON
	e.venueid = v.venueid
INNER JOIN
	public.date as d
ON
	e.dateid = d.dateid
WHERE
	d.month = 'JAN'
	AND d.year = 2008
	AND v.venuestate = 'NY'
GROUP BY
	e.eventname,
	v.venuename,
	d.dateid
ORDER BY total_sales;

--analyzing ticket sales for all pop music concerts for the first quarter of 2008 in the DMV area
SELECT
	d.month,
	e.eventname,
	v.venuename,
	SUM(s.qtysold) as tickets_sold,
	SUM(s.pricepaid) as total_ticket_sales
FROM
	public.sales as s
INNER JOIN
	public.event as e
ON
	s.eventid = e.eventid
INNER JOIN
	public.category as c
ON
	c.catid = e.catid
INNER JOIN
	public.date as d
ON
	e.dateid = d.dateid
INNER JOIN
	public.venue as v
ON 
	e.venueid = v.venueid
WHERE
	e.catid = 9
	AND d.qtr = 1
	AND d.year= 2008
	AND v.venuestate IN ('DC', 'VA', 'MD')
GROUP BY
	d.month,
	e.eventname,
	v.venuename
ORDER BY
	tickets_sold DESC,
	total_ticket_sales DESC;

--querying a list of users from the state of California who likes rock concerts
SELECT
	UPPER(public.users.firstname)|| ' ' || public.users.lastname as names
FROM 
	public.users
WHERE
	public.users.likeconcerts = TRUE
	AND public.users.likerock = TRUE
	AND public.users.state = 'CA'
ORDER BY
	names ASC;

--summary of sales for classical and pop concerts in SEPTEMBER 2008 according to venue
SELECT 
	SUM(s.qtysold) as total_tickets,
	SUM(s.pricepaid) as total_price,
	MIN(s.pricepaid) as minimum_ticket_price,
	AVG(s.pricepaid) as avg_ticket_price,
	MAX(s.pricepaid) as maximum_ticket_price
FROM
	public.event as e
INNER JOIN
	public.category as c
ON
	e.catid = c.catid
INNER JOIN 
	public.date as d
ON
	e.dateid = d.dateid
INNER JOIN
	public.sales as s
ON
	e.eventid = s.eventid
WHERE
	c.catid IN (11, 9)
	AND d.month = 'SEP'
	AND d.year = 2008;

--querying for a listing for Paramore concert tickets, including the venue, city, and relevant ticket information. Paramore is listed as an eventid = 8678.
SELECT 
	e.eventname,
	v.venuename,
	v.venuecity,
	l.numtickets,
	l.priceperticket,
	l.totalprice
FROM
	public.listing as l
INNER JOIN 
	public.event as e
ON
	l.eventid = e.eventid
INNER JOIN
	public.venue as v
ON
	e.venueid = v.venueid
WHERE 
	l.eventid = 8678

--listing of events for NC from March to June of 2008
SELECT d.month,
		e.eventname,
		v.venuename
FROM
	public.event as e
INNER JOIN
	public.date as d
ON 
	e.eventid = d.dateid
INNER JOIN
	public.venue as v
ON 
	e.venueid = v.venueid
WHERE
	v.venuestate = 'NC'
	AND d.month IN ('MAR', 'APR', 'MAY', 'JUN')
	AND d.year = 2008
GROUP BY
	d.month,
	e.eventname,
	v.venuename
ORDER BY 
	d.month;

--group rollup of all-time ticket sales by catid
SELECT 
	c.catname,
	SUM(s.pricepaid) as total_sales
FROM
	public.sales as s
LEFT JOIN 
	public.event as e
ON 
	s.eventid = e.eventid
LEFT JOIN
	public.category as c
ON
	e.catid = c.catid
GROUP BY ROLLUP(c.catname)
ORDER BY
	total_sales;

--listing all the events for venues in Nevada between July and August 2008.
SELECT 
	d.month,
	d.day,
	e.eventname,
	v.venuename,
	v.venuestate
FROM
	public.venue as v
LEFT JOIN
	public.event as e
ON
	e.venueid = v.venueid
LEFT JOIN
	public.date as d
ON 
	d.dateid = e.dateid
WHERE
	v.venuestate = 'NV'
	AND	(d.month = 'JUL' OR d.month = 'AUG')
	AND d.year = 2008
ORDER BY
	d.month ASC,
	v.venuename ASC;