WITH

WITH is a keyword that enables you to form complex queries out of simple named subqueries.

With the way our dataset is structured we can see a lot of information about what’s in the pipeline for sales agents, but we can’t easily see the connection between the managers and the pipeline. However if we create a subquery using WITH we can run many different queries against it and get information for managers about accounts, deal stages, products, and deal values. The ability to use the results of a query as though it were just another table makes WITH a powerful tool for querying. For example, if we wanted to know how which managers had the most closed deals and for which accounts, we could run a query like this:

WITH `full pipeline` AS (
    SELECT sales_teams.manager,
               sales_pipeline.sales_agent,
               sales_pipeline.account,
               sales_pipeline.product,
               sales_pipeline.deal_stage,
               sales_teams.regional_office
      FROM sales_teams, sales_pipeline
     WHERE (sales_pipeline.sales_agent = sales_teams.sales_agent)
)
SELECT manager, regional_office, account,
       COUNT(manager)
  FROM `full pipeline`
 WHERE deal_stage = "Won"
 GROUP BY manager, regional_office, account
 ORDER BY COUNT(manager) DESC, manager, account

The first several rows of the results table would look like:

One restriction on subqueries is that they cannot contain ORDER BY, LIMIT, or OFFSET clauses, but you can still use them in the main query. While you can’t have nested subqueries, you can have multiple named subqueries, and later ones can reference earlier ones. In the following query we are only showing the number of deals per manager from companies that have over 10,000 in revenue:

WITH big_accounts AS (
    SELECT account
      FROM accounts
     WHERE accounts.revenue > 10000
),
pipeline AS (
    SELECT sales_teams.manager,
               sales_pipeline.sales_agent,
               big_accounts.account,
               sales_pipeline.deal_stage,
               sales_teams.regional_office
      FROM sales_teams, sales_pipeline, big_accounts
     WHERE sales_pipeline.sales_agent = sales_teams.sales_agent
           AND big_accounts.account = sales_pipeline.account
)
SELECT manager,
       regional_office,
       account,
       COUNT(manager)
  FROM pipeline
 WHERE deal_stage = "Won"
 GROUP BY manager, regional_office, account
 ORDER BY COUNT(manager) DESC, manager, account

And the results come back as: