Loading this exercise…

Clean campaign imports before attribution

#3 · 3 of 6Free sample

Instructions

Clean campaign imports before attribution

A marketing team needs a trustworthy channel report from campaign_imports. Keep rows with a campaign_id and an ISO activity_date in YYYY-MM-DD format. Normalize channel to lowercase with spaces and hyphens replaced by underscores, then remove duplicate business records after normalization.

Return exactly three columns: channel, spend_usd, and conversions. Return one row per normalized channel, ordered by channel.

Loading the practice editor and dataset…

Sign up to unlock

The reference answer is one attempt away.

Create a free account to save your progress and reveal the worked answer for this free exercise. The complete practice library is available with Practice Pass or Mastery.

Save attempts and completed exercises
Reveal this free exercise's answer after an attempt
Return to your progress from any device
Free forever — no credit card

Create free account →Already a member? Log in →

All exercises in Free Dataset Analysis

ecommerce_orders

6 cols

order_idINTEGER PRIMARY KEY
customer_idINTEGER NOT NULL
order_dateTEXT NOT NULL
statusTEXT NOT NULL
gross_amountREAL NOT NULL
discount_amountREAL NOT NULL

ecommerce_refunds4 cols

refund_idINTEGER PRIMARY KEY
order_idINTEGER NOT NULL
refund_dateTEXT NOT NULL
refund_amountREAL NOT NULL

subscription_accounts2 cols

account_idINTEGER PRIMARY KEY
started_onTEXT NOT NULL

subscription_events5 cols

event_idINTEGER PRIMARY KEY
account_idINTEGER NOT NULL
event_dateTEXT NOT NULL
event_typeTEXT NOT NULL
plan_mrrREAL NOT NULL

campaign_imports6 cols

import_idINTEGER PRIMARY KEY
campaign_idTEXT
activity_dateTEXT NOT NULL
channelTEXT NOT NULL
spend_usdREAL NOT NULL
conversionsINTEGER NOT NULL

inventory_opening2 cols

product_idINTEGER PRIMARY KEY
opening_unitsINTEGER NOT NULL

inventory_movements5 cols

movement_idINTEGER PRIMARY KEY
product_idINTEGER NOT NULL
movement_dateTEXT NOT NULL
movement_typeTEXT NOT NULL
quantity_changeINTEGER NOT NULL

bike_hourly6 cols

observation_idINTEGER PRIMARY KEY
ride_dateTEXT NOT NULL
hourINTEGER NOT NULL
working_dayINTEGER NOT NULL
seasonTEXT NOT NULL
rentalsINTEGER NOT NULL

Tables

8 tables

ecommerce_orders6 cols

order_idINTEGER PRIMARY KEY
customer_idINTEGER NOT NULL
order_dateTEXT NOT NULL
statusTEXT NOT NULL
gross_amountREAL NOT NULL
discount_amountREAL NOT NULL