E-commerce Analytics with Redshift

Build a data warehouse and BI dashboard using Amazon Redshift Serverless.

Introduction

30 Second Summary

Every online purchase generates a trail of data. Transforming that raw data into business insights (which products sell best, which customers spend the most, how revenue changes month to month) is exactly what a Business Intelligence Engineer does day to day.

In this project, you will build a complete analytics pipeline on AWS using Amazon Redshift Serverless. You'll load e-commerce data into a data warehouse, write SQL queries that answer real business questions, and visualize your findings in an interactive dashboard.

What You'll Build

A cloud-based analytics pipeline that takes raw e-commerce transaction data from a ticket marketplace and transforms it into actionable business insights through SQL analysis and interactive dashboards.

By the end of this project, you'll have:

  • A Redshift Serverless data warehouse running in your AWS account, loaded with a star schema dataset of 7 tables simulating an online ticket marketplace.
  • A set of analytical SQL queries using window functions, CTEs, and aggregations that mirror the patterns tested in BIE interviews.
  • A published QuickSight dashboard with charts showing revenue trends, top event categories, and buyer behavior.
  • Secret Mission: a multi-CTE cohort analysis query that identifies power buyers and calculates their lifetime value vs. one-time buyers.

Are there any prerequisites?

You'll need an existing AWS account and basic SQL knowledge (JOINs, WHERE, GROUP BY). This project uses the Redshift Serverless $300 free trial and the Amazon QuickSight 30-day free trial, so there's no cost to complete it.

Set Up Redshift Serverless and Load Sample Data

Every analytics project starts with a place to store and query data. Amazon Redshift Serverless is a fully managed cloud data warehouse that scales automatically and only charges you when you run queries.

In this step, you will activate the Redshift Serverless free trial, connect to the built-in Query Editor, and load a sample e-commerce dataset called TICKIT. This dataset simulates an online ticket marketplace with 7 tables organized in a star schema.

In this step, get ready to:
  • Activate the Redshift Serverless free trial and create a default workgroup.
  • Load the TICKIT sample database using Query Editor v2.
  • Verify that all 7 star schema tables are populated with data.
Activate the Redshift Serverless free trial

Redshift Serverless separates compute and storage into two components. A workgroup handles the compute resources that run your queries. A namespace handles storage (your databases, schemas, and tables).

  • Sign in to the AWS Management Console.
  • In the search bar at the top, type Redshift and select Amazon Redshift from the results.
  • Choose Try Redshift Serverless Free Trial.
  • Under Configuration, choose Use default settings.
  • Choose Save configuration.
  • Wait for the setup to complete. After it finishes, choose Continue to go to your Serverless dashboard.
  • Confirm that the workgroup status shows as Available on the dashboard.

What did the default settings create?

Choosing default settings created a default namespace (storage) with a default workgroup (compute) associated with it. The free trial gives you $300 in credits valid for 90 days.

Redshift Serverless automatically pauses compute when idle, so you only consume credits while running queries.

Don't see the free trial option?

The free trial is only available if your AWS account has never used Redshift Serverless before. If you have used it previously, you can still create a workgroup manually from the Serverless dashboard. Help me set up Redshift Serverless without the free trial wizard.

If the setup seems stuck, give it up to 5 minutes. The workgroup status changes from Creating to Available once compute resources are provisioned.

Connect via Query Editor v2 and load the TICKIT sample data

Query Editor v2 is a browser-based SQL editor built into the Redshift console. It lets you write and run queries without installing anything locally.

  • From the Redshift Serverless dashboard, choose Query data to launch Query Editor v2 in a new browser tab.
  • In the tree-view panel on the left, select the default workgroup.
  • When prompted for authentication, choose Federated user.
  • Choose Create connection.

You should see the connection establish and the workgroup expand in the tree view. Now you will load the sample data.

  • In the tree-view panel, expand the sample_data_dev database under your default workgroup.
  • Select the tickit schema.
  • Choose Open sample notebooks.
  • When prompted to create the sample database, choose Create.

What just happened?

Query Editor v2 loaded the TICKIT sample data into the sample_data_dev database under the tickit schema. It also created a sample notebook named tickit-sample-notebook that contains example queries.

The TICKIT dataset simulates an online ticket marketplace (like StubHub). It contains data about events, venues, buyers, sellers, and ticket sales.

Don't see sample_data_dev in the tree view?

Try clicking the refresh icon at the top of the tree-view panel. If the database still does not appear, make sure you connected to the default workgroup (not a different cluster or workgroup). Help me find the sample_data_dev database in Query Editor v2.

Verify the data loaded correctly

The TICKIT dataset uses a star schema with 7 tables. Two are fact tables (sales, listing) that store transactional data. Five are dimension tables (users, event, venue, category, date) that describe the context around each transaction.

  • In Query Editor v2, open a new query tab by clicking the + icon in the query pane.
  • Make sure sample_data_dev is selected as the database in the dropdown at the top of the editor.
  • Run this query to check the sales table has data:
-- Verify the sales fact table is populated
SELECT COUNT(*) AS total_rows FROM tickit.sales;

What should I see?

You should see a single row with a number in the thousands. This confirms the sales fact table loaded successfully.

  • Now run this query to preview the key columns in the sales table:
-- Preview sales table structure and foreign key relationships
SELECT salesid, pricepaid, qtysold, buyerid, eventid, dateid
FROM tickit.sales
LIMIT 10;

What does this tell you about the star schema?

Each row in sales represents one ticket transaction. The columns buyerid, eventid, and dateid are foreign keys pointing to dimension tables.

This is the heart of a star schema. The fact table sits at the center, and each foreign key links outward to a dimension table that provides descriptive context (who bought it, what event, when it happened).

  • Expand the tickit schema in the tree-view panel to confirm all 7 tables are listed: sales, listing, users, event, venue, category, and date.

Query returning zero rows or an error?

Double-check that you selected sample_data_dev as the database in the dropdown above the query editor (not dev). The TICKIT sample data lives in sample_data_dev, not the default dev database.

If you see a permission error, make sure you completed the Create step when prompted to load the sample database. Help me troubleshoot my TICKIT sample data query.

Your data warehouse is loaded and ready to query. Next up, you will explore the relationships between these tables and write your first business-insight queries using JOINs, GROUP BY, and CASE statements.

Explore the Star Schema and Write Foundational Queries

Your Redshift Serverless data warehouse is running and the TICKIT sample data is loaded. Now it's time to query the data and understand how the tables connect.

The TICKIT dataset models an online ticket marketplace (think StubHub). It uses a star schema with 2 fact tables at the center (sales, listing) and 5 dimension tables around them (users, event, venue, category, date). In this step, you'll write SQL queries that join these tables together and answer real business questions about revenue, top events, and sales trends.

In this step, get ready to:
  • Map the star schema by JOINing fact and dimension tables together.
  • Write GROUP BY queries to answer revenue and sales questions.
  • Use HAVING and CASE to filter and classify results.
Map the dimensional model

Before writing complex analytics queries, you need to understand how the tables relate to each other. The sales fact table sits at the center and connects to dimension tables through foreign keys.

  • In Query Editor v2, click the + icon in the tab row and select Editor to open a new query tab.
  • Make sure sample_data_dev is selected as your database in the dropdown near the top of the editor tab.
  • Paste the following query into the editor to JOIN the sales fact table to three dimension tables:
-- Explore the star schema relationships
-- Join sales fact to event, venue, and category dimensions
SELECT
    s.salesid,
    s.qtysold,
    s.pricepaid,
    e.eventname,
    v.venuename,
    v.venuecity,
    c.catgroup,
    c.catname
FROM tickit.sales s
JOIN tickit.event e ON s.eventid = e.eventid
JOIN tickit.venue v ON e.venueid = v.venueid
JOIN tickit.category c ON e.catid = c.catid
LIMIT 20;

What does this query do?

This query starts from the sales fact table (aliased as s) and JOINs outward to three dimension tables. Each JOIN follows a foreign key relationship.

  • sales → event connects each sale to the event it belongs to (via eventid).
  • event → venue tells you where the event happened (via venueid).
  • event → category tells you what type of event it is (via catid).

LIMIT 20 caps the results so you get a quick preview without loading all rows.

  • Click Run to execute the query.
  • You should see 20 rows in the results pane, each showing data pulled from all four tables (a sale ID, ticket count, price, event name, venue name, venue city, category group, and category name all in one row).

What is the grain of a fact table?

The grain is what one row represents. In the sales table, one row equals one ticket transaction. In the listing table, one row equals one batch of tickets listed for sale.

Understanding the grain helps you write correct aggregations. When you SUM(pricepaid) from sales, you know you're summing across individual transactions.

Not seeing results?

Make sure sample_data_dev is selected as your database in the editor tab dropdown. If you see a schema error, check that you are prefixing table names with tickit. (e.g. tickit.sales).

help me debug my Redshift query

Write business questions as SQL

Now that you understand the table relationships, you can answer real business questions. Each query below targets a different metric that a Business Intelligence Engineer would report on.

  • Clear the editor and paste this query to calculate total revenue by event category:
-- Total revenue by event category
SELECT
    c.catname,
    SUM(s.pricepaid) AS total_revenue,
    SUM(s.qtysold) AS total_tickets
FROM tickit.sales s
JOIN tickit.event e ON s.eventid = e.eventid
JOIN tickit.category c ON e.catid = c.catid
GROUP BY c.catname
ORDER BY total_revenue DESC;

What does this query do?

  • GROUP BY c.catname collapses all sales rows into one row per category.
  • SUM(s.pricepaid) totals up all revenue within each category.
  • ORDER BY total_revenue DESC puts the highest-earning category at the top.
  • Click Run. You should see a short list of categories ranked by revenue. Shows and Concerts appear at the top with the highest totals.
  • Clear the editor and paste this query to find the top 10 events by tickets sold:
-- Top 10 events by tickets sold
SELECT
    e.eventname,
    c.catname,
    SUM(s.qtysold) AS total_tickets,
    SUM(s.pricepaid) AS total_revenue
FROM tickit.sales s
JOIN tickit.event e ON s.eventid = e.eventid
JOIN tickit.category c ON e.catid = c.catid
GROUP BY e.eventname, c.catname
ORDER BY total_tickets DESC
LIMIT 10;

What does this query do?

This groups sales by individual event name (not just category). LIMIT 10 keeps only the top results after ORDER BY total_tickets DESC sorts them highest-first.

  • Click Run. You should see 10 event names with their ticket counts and revenue figures.
  • Clear the editor and paste this query to see revenue broken down by month using the date dimension:
-- Monthly revenue using the date dimension
SELECT
    d.year,
    d.month,
    SUM(s.pricepaid) AS monthly_revenue
FROM tickit.sales s
JOIN tickit.date d ON s.dateid = d.dateid
GROUP BY d.year, d.month
ORDER BY d.year, d.month;

What does this query do?

This JOIN connects sales to the date dimension table. Instead of extracting month/year from a timestamp, you use pre-calculated columns (d.year, d.month) from the dimension table. This is a common pattern in dimensional modeling because it avoids repeated date-parsing logic.

  • Click Run. You should see rows for each year-month combination with their total revenue, ordered chronologically.

Seeing zero rows or an error?

If you see zero rows, double-check that the JOIN uses s.dateid = d.dateid (not s.saletime). The date dimension links through the dateid foreign key, not the timestamp column.

help me fix my date dimension JOIN

Add filtering and conditional logic

GROUP BY answers "how much per category?" but real analysis needs follow-up filters. HAVING lets you filter aggregated results, and CASE lets you classify rows into buckets.

  • Clear the editor and paste this query to find only the high-revenue categories using HAVING:
-- Categories with revenue above threshold (HAVING)
SELECT
    c.catname,
    SUM(s.pricepaid) AS total_revenue
FROM tickit.sales s
JOIN tickit.event e ON s.eventid = e.eventid
JOIN tickit.category c ON e.catid = c.catid
GROUP BY c.catname
HAVING SUM(s.pricepaid) > 10000000
ORDER BY total_revenue DESC;

What is the difference between WHERE and HAVING?

WHERE filters individual rows before aggregation. HAVING filters groups after aggregation. Since total_revenue only exists after the SUM runs, you must use HAVING to filter on it.

  • Click Run. You should see only the categories whose total revenue exceeds 10,000,000. Only a few categories pass this threshold.
  • Clear the editor and paste this query to classify events into revenue tiers using CASE:
-- Classify events by revenue (CASE)
SELECT
    e.eventname,
    SUM(s.pricepaid) AS event_revenue,
    CASE
        WHEN SUM(s.pricepaid) > 5000 THEN 'High Revenue'
        WHEN SUM(s.pricepaid) > 1000 THEN 'Medium Revenue'
        ELSE 'Low Revenue'
    END AS revenue_tier
FROM tickit.sales s
JOIN tickit.event e ON s.eventid = e.eventid
GROUP BY e.eventname
ORDER BY event_revenue DESC
LIMIT 20;

What does CASE do here?

CASE evaluates conditions in order and returns the first match. Events earning above 5000 get labeled High Revenue. Events between 1000 and 5000 get Medium Revenue. Everything else falls into Low Revenue.

This pattern is essential in BIE work because stakeholders want categories and labels, not raw numbers.

  • Click Run. You should see 20 events with their revenue and a revenue_tier column showing High, Medium, or Low.

Getting a syntax error on CASE?

Make sure you have END after your last WHEN/ELSE clause. Also check that AS revenue_tier comes after END, not before it.

help me fix my CASE statement syntax

  • Now save all your queries into one file. Clear the editor, then paste all six queries together (the star schema JOIN, revenue by category, top 10 events, monthly revenue, HAVING filter, and CASE classification).
  • Click Save in the editor toolbar.
  • Enter step2_explore_schema as the title and click Save.

✔️ Awesome, I've got everything!

Great. Double check you've saved the file with all six queries combined.

ⓧ I'd like to double check the full code

-- Explore the star schema relationships
-- Join sales fact to event, venue, and category dimensions
SELECT
    s.salesid,
    s.qtysold,
    s.pricepaid,
    e.eventname,
    v.venuename,
    v.venuecity,
    c.catgroup,
    c.catname
FROM tickit.sales s
JOIN tickit.event e ON s.eventid = e.eventid
JOIN tickit.venue v ON e.venueid = v.venueid
JOIN tickit.category c ON e.catid = c.catid
LIMIT 20;

-- Total revenue by event category
SELECT
    c.catname,
    SUM(s.pricepaid) AS total_revenue,
    SUM(s.qtysold) AS total_tickets
FROM tickit.sales s
JOIN tickit.event e ON s.eventid = e.eventid
JOIN tickit.category c ON e.catid = c.catid
GROUP BY c.catname
ORDER BY total_revenue DESC;

-- Top 10 events by tickets sold
SELECT
    e.eventname,
    c.catname,
    SUM(s.qtysold) AS total_tickets,
    SUM(s.pricepaid) AS total_revenue
FROM tickit.sales s
JOIN tickit.event e ON s.eventid = e.eventid
JOIN tickit.category c ON e.catid = c.catid
GROUP BY e.eventname, c.catname
ORDER BY total_tickets DESC
LIMIT 10;

-- Monthly revenue using the date dimension
SELECT
    d.year,
    d.month,
    SUM(s.pricepaid) AS monthly_revenue
FROM tickit.sales s
JOIN tickit.date d ON s.dateid = d.dateid
GROUP BY d.year, d.month
ORDER BY d.year, d.month;

-- Categories with revenue above threshold (HAVING)
SELECT
    c.catname,
    SUM(s.pricepaid) AS total_revenue
FROM tickit.sales s
JOIN tickit.event e ON s.eventid = e.eventid
JOIN tickit.category c ON e.catid = c.catid
GROUP BY c.catname
HAVING SUM(s.pricepaid) > 10000000
ORDER BY total_revenue DESC;

-- Classify events by revenue (CASE)
SELECT
    e.eventname,
    SUM(s.pricepaid) AS event_revenue,
    CASE
        WHEN SUM(s.pricepaid) > 5000 THEN 'High Revenue'
        WHEN SUM(s.pricepaid) > 1000 THEN 'Medium Revenue'
        ELSE 'Low Revenue'
    END AS revenue_tier
FROM tickit.sales s
JOIN tickit.event e ON s.eventid = e.eventid
GROUP BY e.eventname
ORDER BY event_revenue DESC
LIMIT 20;

You now have a solid foundation of SQL queries that explore the TICKIT star schema from multiple angles. Next up, you'll level up to window functions and CTEs to write the kind of interview-grade SQL that Amazon BIE roles demand.

Write Interview-Grade SQL with Window Functions and CTEs

In Step 2, you explored the TICKIT star schema and wrote foundational queries using GROUP BY, JOIN, HAVING, and CASE. Those patterns answer basic business questions.

BIE interviews go further. They test whether you can rank data within groups, compare values across time, and build layered queries that read like a business narrative. In this step, you will write queries using window functions and CTEs (Common Table Expressions) to produce ranked results, running totals, and month-over-month comparisons.

In this step, get ready to:
  • Use window functions (RANK, ROW_NUMBER, LAG) to rank and compare data within partitions.
  • Build CTEs to layer complex logic into readable, multi-step queries.
  • Combine both patterns into a single business insight query.
Use window functions to rank and compare

Window functions let you perform calculations across a set of rows related to the current row, without collapsing the result into a single group. Think of them as GROUP BY's more powerful sibling.

  • In Query Editor v2, open a new editor tab by clicking the + icon in the tab bar, then choosing Editor.
  • Make sure the sample_data_dev database is selected in the dropdown at the top of the new tab.
  • Paste the following query to rank the top buyers by spend within each event category:
-- RANK: Top buyers by spend within each event category
SELECT
    c.catname,
    u.firstname,
    u.lastname,
    SUM(s.pricepaid) AS total_spend,
    RANK() OVER (PARTITION BY c.catname ORDER BY SUM(s.pricepaid) DESC) AS spend_rank
FROM tickit.sales s
JOIN tickit.event e ON s.eventid = e.eventid
JOIN tickit.category c ON e.catid = c.catid
JOIN tickit.users u ON s.buyerid = u.userid
GROUP BY c.catname, u.firstname, u.lastname
QUALIFY spend_rank <= 5;

What does this query do?

RANK() OVER (PARTITION BY c.catname ...) assigns a rank to each buyer within each category, resetting the count for every new category. The top spender in Shows gets rank 1, and the top spender in Concerts also gets rank 1.

QUALIFY is a Redshift-specific clause that filters on window function results directly. It replaces the need for wrapping everything in a subquery just to add a WHERE on spend_rank.

  • Click Run to execute the query.
  • You should see 5 rows per event category, each showing the buyer's name and their ranked total spend within that category.

Seeing zero rows or an error?

If you see a syntax error mentioning QUALIFY, confirm your database dropdown is set to sample_data_dev. QUALIFY is supported in Redshift Serverless.

If results are empty, check that you joined the tickit.users table on s.buyerid = u.userid (not sellerid).

Help me fix my RANK window function query.

Next, you will use ROW_NUMBER() to find each buyer's very first purchase. This pattern is common in interview questions about "first event" analysis.

  • Clear the editor and paste this CTE query to identify each buyer's first purchase:
-- ROW_NUMBER: Each buyer's first purchase
WITH buyer_purchases AS (
    SELECT
        s.buyerid,
        u.firstname,
        u.lastname,
        s.salesid,
        s.pricepaid,
        s.saletime,
        ROW_NUMBER() OVER (PARTITION BY s.buyerid ORDER BY s.saletime) AS purchase_number
    FROM tickit.sales s
    JOIN tickit.users u ON s.buyerid = u.userid
)
SELECT
    buyerid,
    firstname,
    lastname,
    salesid,
    pricepaid,
    saletime
FROM buyer_purchases
WHERE purchase_number = 1
ORDER BY saletime
LIMIT 20;

What does this query do?

ROW_NUMBER() OVER (PARTITION BY s.buyerid ORDER BY s.saletime) numbers each buyer's purchases in chronological order. The first purchase for every buyer gets purchase_number = 1.

The CTE (WITH buyer_purchases AS (...)) creates a temporary named result set. It makes the query readable by separating the windowed calculation from the final filter.

  • Click Run.
  • You should see 20 rows showing the earliest purchase for 20 different buyers, ordered by saletime.

Now for the most interview-relevant pattern: comparing each period to the previous one. LAG() lets you look at the previous row's value.

  • Clear the editor and paste this query to calculate month-over-month revenue growth:
-- LAG: Month-over-month revenue comparison
WITH monthly_revenue AS (
    SELECT
        d.year,
        d.month,
        SUM(s.pricepaid) AS revenue
    FROM tickit.sales s
    JOIN tickit.date d ON s.dateid = d.dateid
    GROUP BY d.year, d.month
)
SELECT
    year,
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY year, month) AS prev_month_revenue,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY year, month))
        / LAG(revenue) OVER (ORDER BY year, month) * 100, 2
    ) AS mom_growth_pct
FROM monthly_revenue
ORDER BY year, month;

What does this query do?

LAG(revenue) OVER (ORDER BY year, month) returns the revenue from the previous month. The formula (current - previous) / previous * 100 gives the percentage change.

The first row returns NULL for prev_month_revenue and mom_growth_pct because there is no preceding month. This is expected behavior.

  • Click Run.
  • You should see each month's revenue alongside the previous month's value and a percentage change. The first row has NULL values for the comparison columns.

Seeing NULL for every row?

If all rows show NULL for prev_month_revenue, check that your ORDER BY inside the OVER clause uses both year and month. Using only one column breaks the row ordering.

Help me debug my LAG window function query.

Use CTEs to build readable, layered queries

CTEs shine when you need to build on a calculation in stages. Instead of nesting subqueries three levels deep, you name each stage and reference it in the next.

  • Clear the editor and paste this query to calculate a running total of cumulative revenue over time:
-- Running total: Cumulative revenue over time
WITH monthly_revenue AS (
    SELECT
        d.year,
        d.month,
        SUM(s.pricepaid) AS revenue
    FROM tickit.sales s
    JOIN tickit.date d ON s.dateid = d.dateid
    GROUP BY d.year, d.month
)
SELECT
    year,
    month,
    revenue,
    SUM(revenue) OVER (ORDER BY year, month ROWS UNBOUNDED PRECEDING) AS cumulative_revenue
FROM monthly_revenue
ORDER BY year, month;

What does this query do?

SUM(revenue) OVER (ORDER BY year, month ROWS UNBOUNDED PRECEDING) adds up all revenue from the first month through the current month. Each row shows both that month's revenue and the cumulative total so far.

ROWS UNBOUNDED PRECEDING tells Redshift to include every row from the start of the result set up to the current row in the running sum.

  • Click Run.
  • You should see the cumulative_revenue column growing steadily with each month. The final row contains the grand total of all sales.

Next, you will identify buyers who purchased tickets across multiple event categories. This pattern helps segment customer behavior.

  • Clear the editor and paste this query to count how many categories each buyer purchased from:
-- Multi-category buyers
WITH buyer_categories AS (
    SELECT
        s.buyerid,
        COUNT(DISTINCT e.catid) AS categories_purchased
    FROM tickit.sales s
    JOIN tickit.event e ON s.eventid = e.eventid
    GROUP BY s.buyerid
)
SELECT
    categories_purchased,
    COUNT(*) AS num_buyers
FROM buyer_categories
GROUP BY categories_purchased
ORDER BY categories_purchased;

What does this query do?

The CTE counts how many distinct event categories (catid) each buyer purchased from. The outer query then groups buyers by their category count, so you can see the distribution.

This tells you how many buyers are "single-category" fans vs. buyers who explore multiple categories. It is a building block for cohort analysis.

  • Click Run.
  • You should see a small result set (likely 5-10 rows) showing how many buyers purchased from 1 category, 2 categories, and so on.
Combine patterns into a business insight query

The strongest interview answers combine multiple patterns in a single query. This final query chains two CTEs together, then uses both LAG() and RANK() in one SELECT to show each category's monthly performance and relative ranking.

  • Clear the editor and paste this combined query:
-- Combined: Category rank with month-over-month change
WITH category_monthly AS (
    SELECT
        c.catname,
        d.year,
        d.month,
        SUM(s.pricepaid) AS revenue
    FROM tickit.sales s
    JOIN tickit.event e ON s.eventid = e.eventid
    JOIN tickit.category c ON e.catid = c.catid
    JOIN tickit.date d ON s.dateid = d.dateid
    GROUP BY c.catname, d.year, d.month
),
with_growth AS (
    SELECT
        catname,
        year,
        month,
        revenue,
        LAG(revenue) OVER (PARTITION BY catname ORDER BY year, month) AS prev_revenue,
        RANK() OVER (PARTITION BY year, month ORDER BY revenue DESC) AS category_rank
    FROM category_monthly
)
SELECT
    catname,
    year,
    month,
    revenue,
    category_rank,
    ROUND((revenue - prev_revenue) / prev_revenue * 100, 2) AS mom_growth_pct
FROM with_growth
WHERE prev_revenue IS NOT NULL
ORDER BY year, month, category_rank;

What does this query do?

The first CTE (category_monthly) calculates revenue per category per month. The second CTE (with_growth) adds two window functions on top: LAG() partitioned by category for growth, and RANK() partitioned by month to rank categories against each other.

The final SELECT filters out the first month (no previous data) and shows each category's rank and growth side by side. This is the type of multi-layered analysis BIEs produce daily.

  • Click Run.
  • You should see rows showing each category ranked by revenue within each month, alongside a percentage growth figure. Categories like Shows and Concerts should frequently appear in rank 1 or 2.

Getting a division by zero error?

If you see a division error, confirm the WHERE prev_revenue IS NOT NULL clause is present in the final SELECT. This filters out the first month where prev_revenue is NULL.

Help me fix my combined CTE query.

Why CTEs instead of subqueries?

You could write these same queries using nested subqueries, but CTEs make each logical step visible and nameable. In an interview whiteboard scenario, the interviewer can follow your thought process when each stage has a clear label like category_monthly or with_growth.

At Amazon, BIEs chain 3-5 CTEs routinely to build reporting queries. Readability is a feature, not a luxury.

✔️ Awesome, I've got everything!

Great. Make sure you have run all six queries successfully and can see ranked results, running totals, and month-over-month percentages in your output.

ⓧ I'd like to double check the full code

Here is the complete set of queries for this step. Compare yours against this reference:

-- RANK: Top buyers by spend within each event category
SELECT
    c.catname,
    u.firstname,
    u.lastname,
    SUM(s.pricepaid) AS total_spend,
    RANK() OVER (PARTITION BY c.catname ORDER BY SUM(s.pricepaid) DESC) AS spend_rank
FROM tickit.sales s
JOIN tickit.event e ON s.eventid = e.eventid
JOIN tickit.category c ON e.catid = c.catid
JOIN tickit.users u ON s.buyerid = u.userid
GROUP BY c.catname, u.firstname, u.lastname
QUALIFY spend_rank <= 5;

-- ROW_NUMBER: Each buyer's first purchase
WITH buyer_purchases AS (
    SELECT
        s.buyerid,
        u.firstname,
        u.lastname,
        s.salesid,
        s.pricepaid,
        s.saletime,
        ROW_NUMBER() OVER (PARTITION BY s.buyerid ORDER BY s.saletime) AS purchase_number
    FROM tickit.sales s
    JOIN tickit.users u ON s.buyerid = u.userid
)
SELECT
    buyerid,
    firstname,
    lastname,
    salesid,
    pricepaid,
    saletime
FROM buyer_purchases
WHERE purchase_number = 1
ORDER BY saletime
LIMIT 20;

-- LAG: Month-over-month revenue comparison
WITH monthly_revenue AS (
    SELECT
        d.year,
        d.month,
        SUM(s.pricepaid) AS revenue
    FROM tickit.sales s
    JOIN tickit.date d ON s.dateid = d.dateid
    GROUP BY d.year, d.month
)
SELECT
    year,
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY year, month) AS prev_month_revenue,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY year, month))
        / LAG(revenue) OVER (ORDER BY year, month) * 100, 2
    ) AS mom_growth_pct
FROM monthly_revenue
ORDER BY year, month;

-- Running total: Cumulative revenue over time
WITH monthly_revenue AS (
    SELECT
        d.year,
        d.month,
        SUM(s.pricepaid) AS revenue
    FROM tickit.sales s
    JOIN tickit.date d ON s.dateid = d.dateid
    GROUP BY d.year, d.month
)
SELECT
    year,
    month,
    revenue,
    SUM(revenue) OVER (ORDER BY year, month ROWS UNBOUNDED PRECEDING) AS cumulative_revenue
FROM monthly_revenue
ORDER BY year, month;

-- Multi-category buyers
WITH buyer_categories AS (
    SELECT
        s.buyerid,
        COUNT(DISTINCT e.catid) AS categories_purchased
    FROM tickit.sales s
    JOIN tickit.event e ON s.eventid = e.eventid
    GROUP BY s.buyerid
)
SELECT
    categories_purchased,
    COUNT(*) AS num_buyers
FROM buyer_categories
GROUP BY categories_purchased
ORDER BY categories_purchased;

-- Combined: Category rank with month-over-month change
WITH category_monthly AS (
    SELECT
        c.catname,
        d.year,
        d.month,
        SUM(s.pricepaid) AS revenue
    FROM tickit.sales s
    JOIN tickit.event e ON s.eventid = e.eventid
    JOIN tickit.category c ON e.catid = c.catid
    JOIN tickit.date d ON s.dateid = d.dateid
    GROUP BY c.catname, d.year, d.month
),
with_growth AS (
    SELECT
        catname,
        year,
        month,
        revenue,
        LAG(revenue) OVER (PARTITION BY catname ORDER BY year, month) AS prev_revenue,
        RANK() OVER (PARTITION BY year, month ORDER BY revenue DESC) AS category_rank
    FROM category_monthly
)
SELECT
    catname,
    year,
    month,
    revenue,
    category_rank,
    ROUND((revenue - prev_revenue) / prev_revenue * 100, 2) AS mom_growth_pct
FROM with_growth
WHERE prev_revenue IS NOT NULL
ORDER BY year, month, category_rank;

You now have six interview-grade queries covering ranking, first-event detection, period comparison, running totals, behavioral segmentation, and multi-pattern combination. Next up, you will turn these insights into a visual QuickSight dashboard that tells the business story at a glance.

Build a QuickSight Dashboard

You have a data warehouse full of insights. Your SQL queries can answer business questions about revenue, buyer behavior, and trends.

Now it is time to turn those insights into something visual. In this step, you will connect Amazon QuickSight to your Redshift Serverless workgroup and build a dashboard with three charts that tell the story of the TICKIT marketplace.

In this step, get ready to:
  • Sign up for QuickSight and connect it to Redshift Serverless.
  • Create three visualizations: a bar chart, a line chart, and a table.
  • Publish a dashboard combining all three visuals.
Sign up for QuickSight and connect to Redshift

Amazon QuickSight is Amazon's business intelligence tool for creating interactive dashboards. It is the same tool that BIE teams use internally at Amazon to share insights with stakeholders.

  • In the AWS Console search bar at the top, type QuickSight and select the Amazon QuickSight result.
  • You will see a sign-up page since this is your first time using QuickSight. Choose to start the 30-day free trial.
  • Select the Enterprise edition trial.
  • Enter a unique account name for your QuickSight account (e.g. nextwork-analytics).
  • Enter your email address for the notification email.
  • Choose the same AWS Region you used for your Redshift Serverless workgroup for initial SPICE data storage.
  • During setup, make sure to authorize QuickSight to access your Amazon Redshift resources. Check the box next to Amazon Redshift in the list of AWS services.
  • Review your choices and click Create account.
  • Wait for the account to be created, then click Go to Amazon QuickSight to open QuickSight.

What is SPICE?

SPICE (Super-fast, Parallel, In-memory Calculation Engine) is QuickSight's in-memory data store. When you import data into SPICE, your dashboards load faster because queries run against cached data rather than hitting your Redshift cluster directly.

Not seeing the sign-up page?

If your AWS account has previously used QuickSight, you may be taken directly to the QuickSight home page instead of the sign-up flow. In that case, you can skip ahead to creating your dataset.

If you see a permissions error, make sure your IAM user has the necessary QuickSight subscription permissions.

Help me troubleshoot QuickSight sign-up

Now create a dataset that connects QuickSight to your TICKIT data in Redshift Serverless.

  • On the QuickSight start page, click Datasets in the left navigation panel.
  • Click New dataset.
  • Choose the Redshift Auto-discovered icon from the list of data sources.
  • For Data source name, enter tickit-redshift.
  • For Instance ID, select your default workgroup from the dropdown.
  • For Database name, enter sample_data_dev.
  • For UserName, enter the admin username you set up with your Redshift Serverless workgroup.
  • Enter your password in the Password field.
  • Click Validate connection to confirm QuickSight can reach your Redshift workgroup.
  • Once validated, click Create data source.

After creating the data source, QuickSight asks you to choose a schema and table.

  • Select the tickit schema from the Schema dropdown.
  • Select the sales table from the list.
  • Choose Edit/Preview data to open the data preparation page.
  • On the data preparation page, click Switch to Custom SQL tool in the Tables pane.

Why use Custom SQL?

The TICKIT dataset uses a star schema. A single table alone does not have enough context for useful charts.

By using a custom SQL query, you can join the sales fact table to the dimension tables (category, event, users, date) and get rich data for your visualizations.

Connection validation failed?

Make sure your Redshift Serverless workgroup is in the same AWS Region as your QuickSight account. Also verify that you authorized QuickSight to access Amazon Redshift during the sign-up process.

If you skipped the authorization step, go to your profile icon in the top right of QuickSight, select Manage QuickSight, then look for security and permissions settings to enable Redshift access.

Help me fix my QuickSight connection

Create visualizations

With your dataset connected, you will now build three visualizations that answer key business questions about the ticket marketplace.

Start with a bar chart showing total revenue by event category.

  • In the Custom SQL editor, enter a name for your query (e.g. tickit_analysis) and paste the following SQL:
SELECT
    c.catname,
    e.eventname,
    u.firstname,
    u.lastname,
    d.year,
    d.month,
    d.caldate,
    s.pricepaid,
    s.qtysold,
    s.buyerid
FROM tickit.sales s
JOIN tickit.event e ON s.eventid = e.eventid
JOIN tickit.category c ON e.catid = c.catid
JOIN tickit.users u ON s.buyerid = u.userid
JOIN tickit.date d ON s.dateid = d.dateid

What does this query do?

This query joins the sales fact table to four dimension tables: category (for event types), event (for event names), users (for buyer info), and date (for time-based analysis).

This single dataset gives QuickSight everything it needs for all three visualizations you are about to build.

  • Click Confirm query.
  • Select SPICE as the import mode for faster dashboard performance.
  • Click Save & visualize to save your dataset and open the analysis editor.

You are now in the QuickSight analysis editor. This is where you build individual charts (called visuals).

Build your first visual: a bar chart showing total revenue by event category.

  • In the Fields list panel on the left, drag catname to the X axis (or Group/Category) well.
  • Drag pricepaid to the Value well. QuickSight automatically aggregates it as a sum.
  • Make sure the visual type is set to Vertical bar chart. You can change this from the Visual types panel.
  • Click the visual title and rename it to Total Revenue by Event Category.

You should see bars for each category (Shows, Concerts, etc.) with different heights reflecting total revenue.

Now build a line chart showing monthly revenue trends.

  • Click the + Add button at the top of the analysis to add a new visual.
  • Drag caldate to the X axis well. QuickSight may automatically aggregate by month.
  • Drag pricepaid to the Value well.
  • Change the visual type to Line chart from the Visual types panel.
  • If the date is aggregated by day, click the dropdown on the caldate field in the X axis well and select Month to see monthly trends.
  • Rename the visual title to Monthly Revenue Trends.

You should see a line chart with revenue fluctuating over the months in the dataset.

Finally, build a table showing the top 10 buyers by total spend.

  • Click + Add to add another new visual.
  • Change the visual type to Table.
  • Drag firstname and lastname to the Group by well.
  • Drag pricepaid to the Value well (it will appear as Sum of pricepaid).
  • Click the dropdown arrow on the pricepaid value and select Sort descending so the highest spenders appear first.
  • To limit to 10 rows, click Filter in the left panel, add a Top and bottom filter on the visual to show only the top 10 by sum of pricepaid.
  • Rename the visual title to Top 10 Buyers by Total Spend.

Why these three visualizations?

Each chart answers a different type of business question. The bar chart shows category performance (which event types make the most money). The line chart shows trends over time (is revenue growing or declining). The table shows individual-level detail (who are the biggest customers).

Together, they give a stakeholder a complete picture of the business. This is exactly what you would present in a BIE interview when asked to build a dashboard.

Not seeing your fields in the list?

If the Fields list panel is empty or missing columns, your Custom SQL query may not have run successfully. Click the dataset name at the top of the analysis, choose Edit dataset, and verify the SQL query matches what you entered earlier.

Help me fix my missing fields

Combine into a dashboard and publish

Your three visuals are already in a single analysis. Now arrange them into a polished layout and publish the analysis as a shareable dashboard.

  • Drag and resize your three visuals so they are arranged neatly. Place the bar chart and line chart side by side at the top, and the table below them.
  • Click the Title area at the top of the analysis sheet and enter TICKIT Marketplace Analytics.
  • Click Share in the top right corner of the analysis.
  • Select Publish dashboard.
  • Enter a name for your dashboard (e.g. TICKIT Marketplace Dashboard) and click Publish dashboard.

QuickSight creates the dashboard and opens it in view mode. You now have a published, interactive dashboard that anyone with access to your QuickSight account can view.

What is the difference between an analysis and a dashboard?

An analysis is your working canvas where you build and edit visuals. A dashboard is a read-only, published snapshot that you share with stakeholders.

Think of the analysis as a draft and the dashboard as the final report.

Publish option not appearing?

Make sure you have at least one visual in your analysis before publishing. The Share button may not appear if your analysis is empty or if your QuickSight role does not have publish permissions.

Help me find the publish option

Your analytics pipeline is complete. You have gone from raw data in a Redshift data warehouse all the way to a polished, interactive dashboard. Next up, take on the Secret Mission to write advanced cohort analysis queries.

Secret mission

Power Buyer Cohort Analysis

Not all customers are equal. Can you write a multi-CTE query that segments buyers into cohorts based on purchase frequency, calculates their lifetime value, and reveals which event categories each cohort prefers? This is the kind of analysis Amazon BIEs present to stakeholders every week.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

This project uses two AWS services that have free trial periods. Your Redshift Serverless free trial provides $300 in credit over 90 days. Your QuickSight free trial lasts 30 days.

Cost warning

After the free trials expire, Redshift Serverless charges $1.50 per hour of compute (billed per-second, no charges while idle). QuickSight charges $24 per user per month for Author access. If you do not plan to continue using these services, follow the Delete tab below to avoid charges.

Resources you used:

  • Amazon Redshift Serverless workgroup and namespace (consuming free trial credit).
  • Amazon QuickSight account (30-day free trial, then $24/month per Author).
  • TICKIT sample database loaded in the sample_data_dev database.
  • QuickSight dashboard, dataset, and analysis.
  • SQL queries saved in Query Editor v2.

Keep everything running

No action needed. Choose this if you are still actively building or experimenting with your queries and dashboard.

  • Your Redshift Serverless free trial lasts 90 days with $300 in credit. Redshift Serverless automatically pauses when idle, so you are not charged for compute during inactive periods.
  • Your QuickSight free trial lasts 30 days with full Enterprise features.
  • Your saved SQL queries in Query Editor v2 remain available whenever you reconnect.

Pause - I'll come back to this later

Shut down active sessions to conserve your free trial credit, while keeping your data intact for later.

  • Redshift Serverless automatically pauses compute when idle. You only pay for data storage (included in the $300 free trial credit). No manual action is needed to pause Redshift.
  • Close Query Editor v2 browser tabs to end active sessions.
  • Be aware that QuickSight will begin charging $24/month per Author after the 30-day trial ends. Set a calendar reminder to unsubscribe before the trial expires if you do not plan to continue.

Delete - I don't want to use this again

Remove all project resources and cancel both services to avoid any future charges. This permanently removes your data.

Delete the Redshift Serverless workgroup:

  • In the AWS Console, navigate to the Amazon Redshift console.
  • In the left navigation, choose Workgroup configuration under Amazon Redshift Serverless.
  • Select your default workgroup.
  • Choose Actions, then select Delete.
  • Type delete in the confirmation field and select Delete to confirm.

Delete the Redshift Serverless namespace:

  • In the left navigation, choose Namespace configuration.
  • Select your default namespace from the list.
  • Choose Actions, then select Delete namespace.
  • Type delete in the confirmation field and select Delete to confirm. This removes all data in the namespace including the TICKIT sample database.

Unsubscribe from QuickSight:

  • In QuickSight, choose your profile icon on the application bar, then select Manage QuickSight.
  • Choose Account settings.
  • Scroll to Account termination.
  • Toggle off Account termination protection to enable the delete section.
  • Type the confirmation word shown on screen and confirm the deletion. This permanently removes all QuickSight dashboards, analyses, and datasets.

Alternative QuickSight unsubscribe link

If you have trouble finding the account termination page, you can navigate directly to https://us-east-1.quicksight.aws.amazon.com/sn/console/unsubscribe while signed in to AWS. This works regardless of your AWS region.

Nice Work!

Nice Work!

You just built a complete analytics pipeline on AWS, from raw e-commerce data to business insights delivered through a dashboard. This is exactly what Amazon BIE interns do on day one.

You've learned how to:

  • Set up a Redshift Serverless data warehouse with a star schema dataset containing 7 tables (2 fact, 5 dimension) and queried the relationships between them.
  • Written interview-grade SQL using window functions (RANK, ROW_NUMBER, LAG) and CTEs to produce ranked results, running totals, and month-over-month comparisons.
  • Built a QuickSight dashboard with three visualizations communicating revenue trends, top categories, and buyer behavior.
  • Secret Mission: Performed cohort analysis using multi-CTE queries and NTILE() to identify behavioral differences between power buyers and one-time buyers.

Ready to quiz yourself?