Summarising with aggregates: checkpoint

3 tasks you haven't seen before, using what you learnt in this module: count, total and average whole tables with COUNT, SUM, AVG, MIN, MAX and DISTINCT.

Try them without hints first. You can still use a hint if you get stuck, but it costs XP.

Checkpoint · Exercise 1 of 3

London business accounts

How many business customers are there in London, and what's their average credit_limit? Return both numbers in one row.

Downloading the SQL engine…

Ctrl + Enter to run · Ctrl + Shift + Enter to submit

Tables in Brightwell's database

departments3 columns
Brightwell's internal departments.
Columns in departments
Column Type Holds
id INTEGER Unique department number (primary key).
name VARCHAR Department name.
cost_centre VARCHAR Finance cost-centre code, e.g. CC200.

employees11 columns

Everyone who works or has worked at Brightwell, with a manager hierarchy (manager_id).

Columns in employees
Column	Type	Holds
`id`	INTEGER	Unique employee number (primary key).
`first_name`	VARCHAR	First name.
`last_name`	VARCHAR	Last name.
`email`	VARCHAR	Work email address.
`job_title`	VARCHAR	Job title.
`department_id`	INTEGER	Department they work in.
`manager_id`	INTEGER	Their line manager. NULL for the Managing Director.
`region`	VARCHAR	Sales region covered. Only set for the sales team.
`hire_date`	DATE	Date they joined.
`leave_date`	DATE	Date they left. NULL if they still work here.
`salary`	DECIMAL(12,2)	Annual salary in £.

suppliers6 columns

Companies Brightwell buys its stock from.

Columns in suppliers
Column	Type	Holds
`id`	INTEGER	Unique supplier number (primary key).
`name`	VARCHAR	Supplier company name.
`country`	VARCHAR	Country.
`city`	VARCHAR	City.
`contact_email`	VARCHAR	Ordering contact email. NULL where unknown.
`payment_terms_days`	INTEGER	Days Brightwell has to pay the supplier's invoices.

products12 columns

The product catalogue. Prices are current list prices, excluding VAT.

Columns in products
Column	Type	Holds
`id`	INTEGER	Unique product number (primary key).
`sku`	VARCHAR	Stock code, e.g. BW-PAP-1004.
`name`	VARCHAR	Product name.
`category`	VARCHAR	Product category.
`subcategory`	VARCHAR	Product subcategory.
`supplier_id`	INTEGER	Main supplier. NULL for a few products (data gap).
`unit_cost`	DECIMAL(12,2)	What Brightwell pays per unit, £ ex VAT.
`list_price`	DECIMAL(12,2)	Current selling price per unit, £ ex VAT.
`stock_qty`	INTEGER	Units in stock now.
`reorder_level`	INTEGER	Reorder when stock falls to this level.
`is_discontinued`	BOOLEAN	No longer sold.
`launched_on`	DATE	Date the product was first sold.

customers13 columns

Business accounts (wholesale) and consumers (online shop). Contains duplicate customers and inconsistent text on purpose.

Columns in customers
Column	Type	Holds
`id`	INTEGER	Unique customer number (primary key).
`name`	VARCHAR	Company name (Business) or person's name (Consumer).
`customer_type`	VARCHAR	'Business' or 'Consumer'.
`contact_name`	VARCHAR	Main contact at a business. NULL for consumers.
`email`	VARCHAR	Email address.
`phone`	VARCHAR	Phone number, in whatever format it was typed.
`city`	VARCHAR	City or town.
`region`	VARCHAR	UK region.
`postcode`	VARCHAR	Postcode.
`signup_date`	DATE	Date the account was opened.
`account_manager_id`	INTEGER	Sales rep who looks after a business account. NULL for consumers and some small business accounts.
`credit_limit`	DECIMAL(12,2)	Credit limit in £ for business accounts. NULL for consumers.
`marketing_opt_in`	BOOLEAN	Agreed to receive marketing emails.

marketing_campaigns7 columns

Marketing campaigns, their dates and budgets.

Columns in marketing_campaigns
Column	Type	Holds
`id`	INTEGER	Unique campaign number (primary key).
`name`	VARCHAR	Campaign name.
`channel`	VARCHAR	Email, Paid Search, Social, Print or Trade Show.
`target_segment`	VARCHAR	'Business', 'Consumer' or 'All'.
`start_date`	DATE	First day.
`end_date`	DATE	Last day.
`budget`	DECIMAL(12,2)	Spend in £.

orders9 columns

Customer orders. One row per order; the lines are in order_items.

Columns in orders
Column	Type	Holds
`id`	INTEGER	Unique order number (primary key).
`customer_id`	INTEGER	Who placed the order.
`order_date`	DATE	Date ordered.
`channel`	VARCHAR	'Online', 'Phone' or 'Sales Rep'.
`status`	VARCHAR	'Completed', 'Pending', 'Cancelled' or 'Returned'.
`employee_id`	INTEGER	Employee who took the order. NULL for online orders.
`campaign_id`	INTEGER	Campaign the order is attributed to. NULL if none.
`shipped_date`	DATE	Date dispatched. NULL if cancelled or not shipped yet.
`shipping_cost`	DECIMAL(12,2)	Delivery charge in £ ex VAT.

order_items5 columns

Order lines. Revenue for a line is quantity × unit_price (the price actually charged, ex VAT).

Columns in order_items
Column	Type	Holds
`id`	INTEGER	Unique order line number (primary key).
`order_id`	INTEGER	The order this line belongs to.
`product_id`	INTEGER	Product sold.
`quantity`	INTEGER	Units ordered.
`unit_price`	DECIMAL(12,2)	Price charged per unit at the time, after any discount, £ ex VAT.

invoices9 columns

One invoice per shipped order. Amounts in £.

Columns in invoices
Column	Type	Holds
`id`	INTEGER	Unique invoice number (primary key).
`order_id`	INTEGER	Order invoiced.
`customer_id`	INTEGER	Customer billed.
`invoice_date`	DATE	Date issued.
`due_date`	DATE	Date payment is due.
`net_amount`	DECIMAL(12,2)	Goods plus shipping, ex VAT.
`vat_amount`	DECIMAL(12,2)	VAT at 20%.
`total_amount`	DECIMAL(12,2)	net_amount + vat_amount.
`status`	VARCHAR	'Paid', 'Part paid', 'Unpaid' or 'Credited' (order returned).

payments5 columns

Money received against invoices. Refunds are negative amounts.

Columns in payments
Column	Type	Holds
`id`	INTEGER	Unique payment number (primary key).
`invoice_id`	INTEGER	Invoice paid.
`payment_date`	DATE	Date received.
`amount`	DECIMAL(12,2)	Amount in £.
`method`	VARCHAR	'Card', 'BACS', 'Direct Debit' or 'Cheque'.

web_sessions12 columns

Visits to the online shop, tracked from 01/04/2025. Each row is one visit and how far it got.

Columns in web_sessions
Column	Type	Holds
`id`	INTEGER	Unique session number (primary key).
`session_start`	TIMESTAMP	When the visit started (UK time).
`customer_id`	INTEGER	Logged-in customer. NULL for anonymous visitors.
`source`	VARCHAR	Organic, Direct, Paid Search, Email, Social or Referral.
`campaign_id`	INTEGER	Campaign that brought the visitor. NULL if none.
`device`	VARCHAR	Desktop, Mobile or Tablet.
`landing_page`	VARCHAR	First page seen.
`pages_viewed`	INTEGER	Pages viewed in the visit.
`viewed_product`	BOOLEAN	Viewed at least one product page.
`added_to_basket`	BOOLEAN	Added something to the basket.
`reached_checkout`	BOOLEAN	Started checkout.
`order_id`	INTEGER	Order placed in this visit. NULL if none.

Loading…