This SQL query is designed to extract and aggregate information...

September 4, 2025 at 03:29 PM

SELECT

'Optouts' as "Type",

quote.QUOTE_NUMBER as "Quote Number",

quote.DATEOFORDERSUBMISSION_QUOTE as "Submitted Date",

quote.EVOOPTOUTSTATUS_QUOTE as "Optout Status",

quote.CRMOPPORTUNITYID_QUOTE as "Opportunity ID",

quote.EVOELIGIBLITYMETHOD_QUOTE as "Onboarding Method",

quote.EVOELIGIBILTYOPTOUT_QUOTE as "Opt Out Request Checkbox",

quote.EVOELIGIBILTYOPTOUTMESSAGE as "Optout Reason",

quote.SALESOFFICEREGION_QUOTE as "Sales Office Region",

quote.PREPAREDBY_NAME as "Sales Associate",

quote.SALESOFFICECODE_QUOTE as "Salesoffice_code",

quote.PROCESSINGCENTERS_QUOTE as "Processing Region",

quote.APPROVALQUEUE as "Approval Queue",

quote.MANAGERNAME_QUOTE as "Manager Name",

quote.DVPEMAIL_QUOTE as "DVP Email",

office.se_name as "SE",

office.vp_name as "VP",

office.dvp_name as "DVP",

office.se2_name as "SE2",

office.vp2_name as "VP2",

office.dvp2_name as "DVP2",

office.svp_name as "SVP",

case

when quote.EVOOPTOUTSTATUS_QUOTE = 'Approved' then 1 else 0 end as "Opt-out Count",

case

when quote.EVOOPTOUTSTATUS_QUOTE is null or quote.EVOOPTOUTSTATUS_QUOTE = 'Rejected' then 1 else 0 end as "Eligible Count",

case

when quote.EVOELIGIBLITYMETHOD_QUOTE in ('Digital','DigitalPlus') then 1 else 0 end as "Digital Count"

FROM

self_service.sv_cpq_mas_sbs_nas_can_quote quote

left join self_service.sv_sfdc_users users on quote.PREPAREDBY_NAME = users.name

left join sales_dw.officemap office on quote.SALESOFFICECODE_QUOTE = office.sales_office

join (

select distinct

max(quote.DATEOFORDERSUBMISSION_QUOTE) as maxdate,

quote.QUOTE_NUMBER

from

self_service.sv_cpq_mas_sbs_nas_can_quote quote

group by

quote.QUOTE_NUMBER

) maxdate on quote.QUOTE_NUMBER = maxdate.QUOTE_NUMBER

and quote.DATEOFORDERSUBMISSION_QUOTE = maxdate.maxdate

WHERE

UPPER(quote.HASRUN_QUOTE) = 'TRUE'

AND UPPER(quote.PROCESSINGCENTERS_QUOTE) != 'SBS - TEST'

AND UPPER(quote.ORDERTYPE_QUOTE) IN ('NEW CLIENT','ADDITIONAL CONTROL')

AND quote.DATEOFORDERSUBMISSION_QUOTE BETWEEN to_date('MM/dd/YYYY','07/01/2022') AND CURRENT_DATE

AND FORMATDATE('yyyy.MM.dd', quote.CREATEDDATE_QUOTE) >='2022.07.01'

AND quote.PROCESSINGCENTERS_QUOTE <> 'SBS - ACQUISITIONS4'

AND quote.PROCESSINGCENTERS_QUOTE <> 'SBS - ACQUISITIONS1'

group by

quote.QUOTE_NUMBER,

quote.DATEOFORDERSUBMISSION_QUOTE,

quote.EVOOPTOUTSTATUS_QUOTE,

quote.CRMOPPORTUNITYID_QUOTE,

quote.EVOELIGIBLITYMETHOD_QUOTE,

quote.EVOELIGIBILTYOPTOUT_QUOTE,

quote.EVOELIGIBILTYOPTOUTMESSAGE,

quote.SALESOFFICEREGION_QUOTE,

quote.PREPAREDBY_NAME,

quote.SALESOFFICECODE_QUOTE,

quote.PROCESSINGCENTERS_QUOTE,

quote.APPROVALQUEUE,

quote.MANAGERNAME_QUOTE,

quote.DVPEMAIL_QUOTE,

office.se_name,

office.vp_name,

office.dvp_name,

office.se2_name,

office.vp2_name,

office.dvp2_name,

office.svp_name

This SQL query is designed to extract and aggregate information about "opt-outs" from a sales-related dataset. Here's a breakdown of what the code does:

Purpose: The query retrieves data about quotes for clients who may have requested "opt-outs" in the sales process, including details such as submission dates, opt-out statuses, onboarding methods, sales office regions, and associated employee details like managers and sales associates.
Columns Selected:
- Provides information about the quotes (quote.QUOTE_NUMBER, quote.DATEOFORDERSUBMISSION_QUOTE, etc.).
- Includes specific attributes of opt-outs, like opt-out status (quote.EVOOPTOUTSTATUS_QUOTE), opt-out reason (quote.EVOELIGIBILTYOPTOUTMESSAGE), and whether opt-out was requested or eligible.
- Includes sales personnel and organizational hierarchy (office.se_name, office.vp_name, etc.).
- Computes:
  - "Opt-out Count": A count of quotes where the opt-out status is "Approved".
  - "Eligible Count": A count of quotes that are ineligible (EVOOPTOUTSTATUS_QUOTE is null or "Rejected").
  - "Digital Count": Quotes with onboarding methods related to digital processes (Digital or DigitalPlus).
Data Sources:
- The main data is extracted from the table self_service.sv_cpq_mas_sbs_nas_can_quote (aliased as quote).
- Joins additional data:
  - self_service.sv_sfdc_users for user details (matching on PREPAREDBY_NAME).
  - sales_dw.officemap for office details (matching on SALESOFFICECODE_QUOTE).
Subquery:
- There is a subquery that identifies the maximum submission date for each quote (maxdate). This ensures only the most recent submission date for each quote is included in the final result.
Filters: The WHERE clause applies several filters to ensure only relevant data is processed:
- The HASRUN_QUOTE field must be 'TRUE'.
- Excludes rows from specific processing centers.
- Filters for specific order types (NEW CLIENT or ADDITIONAL CONTROL).
- Limits the submission date between 07/01/2022 and the current date.
- Ensures rows have a CREATEDDATE_QUOTE after 2022.07.01 (converted to format 'yyyy.MM.dd').
Grouping: The query uses GROUP BY to aggregate and organize the data by fields relevant to the quotes and summary counts.

Summary:

This query pulls a detailed report on opt-out requests associated with quotes in the sales system. It filters out irrelevant data, calculates summary fields (e.g., counts for opt-outs, eligibility, and digital methods), and joins additional metadata like user and office details. It is useful for analyzing opt-out trends and their associated sales characteristics over a specific timeframe.

Generate your own explanations

Download our vscode extension

Read other generated explanations

Built by @thebuilderjr