This repository contains four independent analytical SQL queries built on a shared e-commerce database. Each query addresses a specific business question and demonstrates different SQL techniques: aggregations, joins, window functions, CTEs, UNNEST, and UNION ALL.
All queries are written in BigQuery SQL.
-
SQL (BigQuery)
-
Window functions (
SUM OVER,DENSE_RANK) -
CTEs,
UNION ALL,UNNEST -
Conditional aggregation (
CASE WHEN)
The database includes:
-
Transactional tables:
order,product -
Session & event data:
session,session_params,event_params -
Marketing & forecasting:
paid_search_cost,revenue_predict
For each continent, calculate total revenue, revenue from the "Bookcases & shelving units" category, and the percentage share of this category.
SQL techniques: multiple table joins, conditional aggregation with CASE WHEN, percentage calculation, GROUP BY with ORDER BY.
Calculate the percentage of monthly paid search costs relative to total costs across the entire period.
SQL techniques: date extraction with EXTRACT, subquery aggregation, window function SUM() OVER() for analytical percentage calculation.
Calculate the percentage of events where session_engaged = 1 out of all events where this value is not NULL, grouped by device.
SQL techniques: CTE, working with nested fields using UNNEST, conditional counting, behavioral segmentation by device.
Calculate cumulative actual revenue vs cumulative predicted revenue by date and compute the percentage of target achievement.
SQL techniques: UNION ALL to combine actual and forecasted data, cumulative sums with SUM() OVER(ORDER BY date), revenue progress tracking.
-
Clone this repository
-
Open any query from the
sql/folder -
Run in BigQuery — each query is independent and can be executed separately
e-commerce-sql-practice/
├── sql/
│ ├── query1.sql
│ ├── query2.sql
│ ├── query3.sql
│ └── query4.sql
├── docs/
│ └── schema.png
└── README.md