Workspace
Akash Soni/

Analyzing Ticket Sales Data with Amazon Redshift

0
Beta
Spinner

Analyzing Online Ticket Sales with Amazon Redshift

In this workspace, we will be accessing data stored in Amazon Redshift, a data warehouse product that is part of Amazon Web Services. More specifically, we'll be analyzing sales activity from a fictional ticketing website where users both buy and sell tickets online for sporting events, shows, and concerts (source).

To consult the solution, head over to the file browser and select notebook-solution.ipynb.

Explore events

Unknown integration
DataFrameavailable as
df
variable
-- List all the events
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

This is linking up to several other tables in the warehouse, such as venue, category and date. Let's join things up.

Unknown integration
DataFrameavailable as
df
variable
SELECT *
FROM event
INNER JOIN ___ USING(venueid)
INNER JOIN ___ USING(catid)
INNER JOIN ___ USING(dateid)
LIMIT 100
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

There's a starttime column coming from the event table and there's also a caldate column, coming from the date table. Let's see what's up with this.

Unknown integration
DataFrameavailable as
df
variable
SELECT
	CASE WHEN ___ THEN ___ ELSE ___ END AS same_date,
	___
FROM event
INNER JOIN date USING(dateid)
GROUP BY 1
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
Unknown integration
DataFrameavailable as
df
variable
SELECT ___
FROM event
INNER JOIN date USING(dateid)
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

Let's see how much events are happening in different cities.

Unknown integration
DataFrameavailable as
df
variable
SELECT 
	___,
    ___ AS ___
FROM event
INNER JOIN ___ USING(___)
GROUP BY 1
ORDER BY 2 DESC
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

Explore listings and sales

Unknown integration
DataFrameavailable as
df
variable
-- show 100 listing records
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
Unknown integration
DataFrameavailable as
df
variable
-- show 100 sales records
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.

Let's see if multiple sales can happen for the same listing.

Unknown integration
DataFrameavailable as
ticket_prices
variable
WITH listings_with_sales AS (
    SELECT 
        ___,
        ___ AS ___
    FROM listing
	INNER JOIN sales USING(listid)
    GROUP BY 1
)
SELECT
	___,
    ___
FROM listings_with_sales
GROUP BY 1
ORDER BY 1
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.



  • AI Chat
  • Code