SQL Training for Ecommerce Professionals

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

How to start the training:

SQL for Ecommerce Professionals

Basic SQL

1. Basic select statement

2. Choosing specific fields from a table

3. Using aliases

4. Eliminating duplicates

5. Sorting

6. Aggregations (think pivots in Microsoft Excel)

7. Aggregations

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

Intermediate SQL

1. Keyword brand vs. non-brand performance

2. Keyword matchtype performance

Advanced SQL

1. Advertising vs. overall sales

2. Top keywords in a campaign that is driving revenue

3. Products generating 80% of overall sales

4. Keywords generating 80% of advertising sales

Interested in learning more about Intentwise Analytics Cloud?

How to start the training:

SQL for Ecommerce Professionals

Basic SQL

1. Basic select statement

2. Choosing specific fields from a table

3. Using aliases

4. Eliminating duplicates

5. Sorting

6. Aggregations (think pivots in Microsoft Excel)

7. Aggregations

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

Intermediate SQL

1. Keyword brand vs. non-brand performance

2. Keyword matchtype performance

Advanced SQL

1. Advertising vs. overall sales

2. Top keywords in a campaign that is driving revenue

3. Products generating 80% of overall sales

4. Keywords generating 80% of advertising sales

Interested in learning more about Intentwise Analytics Cloud?

Sign Up for Intentwise Emails