Close icon

How to start the training:

Sign up for a SQL Lab account with the form below.

Watch our on-demand SQL 101 training, following along with the examples below.

The best way to learn is to practice! Run the intermediate and advanced queries below using your SQL Lab account.

SQL for Ecommerce Professionals

This is a hands-on training for ecommerce professionals to learn the basics of Structured Query Language (SQL).

Basic SQL

1. Basic select statement

Copied to clipboard! 

  select * 
  from campaign 
  limit 10

2. Choosing specific fields from a table

Copied to clipboard! 

  select date, campaign_name, spend
  from campaign
  limit 10

3. Using aliases

Copied to clipboard! 

  select a.date, a.campaign_name, a.spend
  from campaign a
  limit 10

4. Eliminating duplicates

Copied to clipboard! 

  select distinct date
  from campaign

5. Sorting

Copied to clipboard! 

  select distinct date
  from campaign
  order by 1 desc

6. Aggregations (think pivots in Microsoft Excel)

Copied to clipboard! 

  select  date, sum(spend)
  from campaign
  group by 1
  order by 1 desc

7. Aggregations

Copied to clipboard! 

  select campaign_type , sum(spend)
  from campaign
  group by 1
  order by 1 desc

8. Joining two tables (think vlookups in Microsoft Excel)

Copied to clipboard! 

  select a.keyword, a.match_type, b.campaign_name, 
  b.campaign_type, sum(a.spend) as spend, 
  sum(a.revenue) as ad_revenue
  from keyword a
  left join campaign b
  ON
  a.campaign_id = b.campaign_id
  Group by 1,2,3,4

Intermediate SQL

1. Keyword brand vs. non-brand performance

Copied to clipboard! 

  select campaign_type,brand_type,
  sum(k.spend) as spend,
  sum(k.revenue) as revenue
  from campaign c
  left join keyword k
  on c.campaign_name = k.campaign_name
  where c.DATE > '05-01-2022'
  and k.spend > 0
  group by 1,2
  order by 3 desc

2. Keyword matchtype performance

Copied to clipboard! 

  select campaign_type,brand_type,
  sum(k.spend) as spend,
  sum(k.revenue) as revenue
  from campaign c
  left join keyword k
  on c.campaign_name = k.campaign_name
  where c.DATE > '05-01-2022'
  and k.spend > 0
  group by 1,2
  order by 3 desc

Advanced SQL

1. Advertising vs. overall sales

Copied to clipboard! 

  select s.asin, s.title, ad_revenue,
  round((100 * ad_revenue)/(sum(ad_revenue) over()), 1) 
  ad_revenue_percentage,
  overall_sales, 
  (100 * overall_sales)/(sum(overall_sales) over())
  as product_sales_percentage 
  from (
  select asin, title, sum(product_sales) 
  as overall_sales from sales s
  where date > current_date-30
  group by 1,2)s join
  (select asin, title, sum(revenue) as ad_revenue 
  from product p
  where date > current_date-30
  group by 1,2)p on s.asin = p.asin

2. Top keywords in a campaign that is driving revenue

Copied to clipboard! 

  select keyword, campaign_name, revenue
  from (
  select keyword, campaign_name, revenue,
  row_number() over (partition by campaign_name 
  order by revenue desc)
  as revenue_order_count
  from (
  select keyword, campaign_name, sum(revenue) as revenue 
  from keyword k
  where date > current_date-30
  group by 1,2)k
  )a
  where revenue_order_count <= 3
  order by campaign_name desc, revenue desc

3. Products generating 80% of overall sales

Copied to clipboard! 

  select asin, title,
  100 * product_sales/(SUM(product_sales) OVER ()) 
  as product_sales_percentage
  from (
  select asin, title, sum(product_sales) as product_sales 
  from sales s
  where date > current_date-30
  group by 1,2)s

4. Keywords generating 80% of advertising sales

Copied to clipboard! 

  select keyword, match_type, campaign_name,
  100 * revenue/(SUM(revenue) OVER ()) 
  as revenue_percentage 
  from (
  select keyword, match_type, campaign_name, 
  sum(revenue) as revenue
  from keyword k
  where date > current_date-30
  group by 1,2,3)k

Interested in learning more about Intentwise Analytics Cloud?