Sample Snowflake Queries

Query 1: All product spend deployed by Nike

Required Licenses: Spend (Detailed)

SELECT DISTINCT 
products.name "Product Name", 
intricately_product_deployments.product_category "Product Category", 
intricately_product_deployments.spend "Monthly Product Spend" 
FROM intricately_companies products 
INNER JOIN intricately_product_deployments ON intricately_product_deployments.product_slug = products.company_slug 
INNER JOIN intricately_companies ON intricately_companies.company_slug = intricately_product_deployments.company_slug 
WHERE 
intricately_companies.company_slug = 'nike' AND 
intricately_product_deployments.spend IS NOT NULL;

Query 2: All Akamai products and spend deployed by Nike

Required Licenses: Spend (Detailed)

SELECT 
products.name "Product Name", 
products.company_slug "Product Slug", 
intricately_product_deployments.product_category "Product Category Name", 
intricately_product_deployments.spend "Monthly Product Spend" 
FROM intricately_companies products 
INNER JOIN intricately_product_deployments ON intricately_product_deployments.product_slug = products.company_slug 
INNER JOIN intricately_companies companies ON companies.company_slug = intricately_product_deployments.company_slug 
INNER JOIN providers_products ON providers_products.product_slug = intricately_product_deployments.product_slug 
WHERE 
intricately_product_deployments.company_slug = 'nike' AND 
providers_products.provider_slug IN ('akamai-aeaa7a0d-1e11-4599-9424-774a2c8fe0ae'); -- Akamai product slug

Query 3: All domains operated by Nike (nike.com)

Required Licenses: Digital Relationship

SELECT 
domains.domain "Domain" 
FROM intricately_domains domains 
INNER JOIN intricately_companies ON intricately_companies.company_slug = domains.company_slug 
INNER JOIN intricately_domains ON intricately_domains.company_slug = intricately_companies.company_slug 
WHERE 
intricately_domains.domain = 'nike.com';

Query 4: Wallet-share analysis: Cloud Hosting vs. AWS vs. Azure vs. GCP

Required Licenses: Spend (Detailed)

SELECT DISTINCT 
companies.name "Company Name", 
companies.intricately_url "Intricately URL", 
( 
SELECT COALESCE(SUM(spend),0) 
FROM intricately_product_deployments 
WHERE 
company_slug = companies.company_slug AND 
product_category = 'Cloud Hosting' 
) "Cloud Hosting Spend (Monthly)", 
( 
SELECT COALESCE(SUM(spend),0) 
FROM intricately_product_deployments product_deployments 
INNER JOIN intricately_providers_products ON intricately_providers_products.product_slug = product_deployments.product_slug 
WHERE 
product_deployments.company_slug = companies.company_slug AND 
provider_slug = 'amazon-web-services' 
) "AWS Spend (Monthly)", 
( 
SELECT COALESCE(SUM(spend),0) 
FROM intricately_product_deployments product_deployments 
INNER JOIN intricately_providers_products ON intricately_providers_products.product_slug = product_deployments.product_slug 
WHERE 
product_deployments.company_slug = companies.company_slug AND 
provider_slug = 'microsoft-azure-provider' 
) "Azure Spend (Monthly)", 
( 
SELECT COALESCE(SUM(spend),0) 
FROM intricately_product_deployments product_deployments 
INNER JOIN intricately_providers_products ON intricately_providers_products.product_slug = product_deployments.product_slug 
WHERE 
product_deployments.company_slug = companies.company_slug AND 
provider_slug = 'google-cloud-platform' 
) "Google Cloud Spend (Monthly)" 
FROM intricately_companies companies 
INNER JOIN intricately_product_deployments product_deployments ON product_deployments.company_slug = companies.company_slug 
ORDER BY 3 DESC 
LIMIT 10;

Query 5: Wallet-share analysis by product category

SELECT DISTINCT 
companies.name "Company Name", 
companies.intricately_url "Intricately URL", 
( 
SELECT COALESCE(SUM(spend),0) 
FROM intricately_product_deployments 
WHERE company_slug = companies.company_slug AND product_category = 'Cloud Hosting' 
) "Cloud Hosting Spend (Monthly)", 
( 
SELECT COALESCE(SUM(spend),0) 
FROM intricately_product_deployments 
WHERE company_slug = companies.company_slug AND product_category = 'Hosting' 
) "Data Center Spend (Monthly)", 
( 
SELECT COALESCE(SUM(spend),0) 
FROM intricately_product_deployments 
WHERE company_slug = companies.company_slug AND product_category IN ('Content Delivery','Security') 
) "CDN & Security Spend (Monthly)", 
( 
SELECT COALESCE(SUM(spend),0) 
FROM intricately_product_deployments 
WHERE company_slug = companies.company_slug AND product_category IN ('DNS','Traffic Management') 
) "DNS & GTM Spend (Monthly)" 
FROM intricately_companies companies 
INNER JOIN intricately_product_deployments product_deployments ON product_deployments.company_slug = companies.company_slug 
ORDER BY 3 DESC 
LIMIT 10;

Query 6: AWS vs. Azure vs. GCP customer wins by industry

SELECT 
companies.primary_industry "Industry", 
providers.name "Provider", 
COUNT(companies.slug) "Customers" 
FROM intricately_product_deployments products 
INNER JOIN intricately_companies companies ON companies.slug = products.company_slug 
INNER JOIN providers_products ON providers_products.product_slug = products.product_slug 
INNER JOIN intricately_companies providers ON providers.slug = providers_products.provider_slug 
WHERE 
companies.employees_count > 50 AND 
length(companies.primary_industry) > 0 
AND providers_products.provider_slug IN ('google-cloud-platform','amazon-web-services','microsoft-azure-provider') AND 
products.deployed_on > '01/01/2017' 
GROUP BY 1, 2 
ORDER BY 3 DESC;

Query 7: AWS vs. Azure vs. GCP customers wins over time by company size

Required Licenses: Spend (Detailed), Historical

SELECT 
DATE_TRUNC('MONTH', products.deployed_on) "Date (Month)", 
CASE 
WHEN companies.employees_count < 10 THEN 'Tiny (< 10)' 
WHEN companies.employees_count < 100 THEN 'Startup (10 - 100)' 
WHEN companies.employees_count < 1000 THEN 'Mid-Market (100 - 1000)' 
ELSE 'Enterprise (1000+)' 
END "Company Size", 
providers.name "Provider", 
COUNT(companies.slug) "Customers" 
FROM intricately_product_deployments products 
INNER JOIN intricately_companies companies ON companies.slug = products.company_slug 
INNER JOIN providers_products ON providers_products.product_slug = products.product_slug 
INNER JOIN intricately_companies providers ON providers.slug = providers_products.provider_slug 
WHERE 
providers_products.provider_slug IN ('google-cloud-platform', 'amazon-web-services', 'microsoft-azure-provider') AND 
products.deployed_on > '01/01/2017' 
GROUP BY 1, 2, 3 
ORDER BY 1, 2, 3 DESC;

Query 8: Companies moving from the Data Center to the Cloud

Required Licenses: Spend (Detailed)

While it's not really possible to determine when a company will move application workloads from the data center to the cloud, we can identify those companies who are best positioned to take on such a lift and shift.

The following query identifies companies with both a substantial data center and cloud hosting presence and are deployed with at least one of the major US-based cloud providers (AWS, Microsoft Azure, or Google Cloud Platform).

SELECT DISTINCT 
companies.name "Company Name", 
companies.hosting_monthly_spend "Data Center Spend (Monthly)", 
companies.cloud_hosting_monthly_spend "Cloud Hosting Spend (Monthly)" 
FROM intricately_companies companies 
INNER JOIN intricately_product_deployments product_deployments on product_deployments.company_slug = companies.company_slug 
INNER JOIN providers_products providers on providers.product_slug = product_deployments.product_slug 
WHERE 
companies.hosting_monthly_spend > 10000 AND 
companies.cloud_hosting_monthly_spend > 10000 AND 
companies.spend_ability_rank < 7 AND -- spend potential greater than $1k/mo. 
providers.provider_slug in ('amazon-web-services','microsoft-azure-provider','google-cloud-platform') 
ORDER BY companies.hosting_monthly_spend DESC 
LIMIT 100;

Query 9: Company Spend Growth

Required Licenses: Spend (Detailed), Historical

WITH DATA AS ( 
SELECT 
DATE_TRUNC('MONTH', products.deployed_on) "Date", 
products.product_category "Product Category", 
sum(products.spend) * 12 "Annual Spend" 
FROM intricately_product_deployments products 
LEFT OUTER JOIN intricately_domains domains on domains.company_slug = products.company_slug 
WHERE domains.domain = 'etsy.com' 
AND products.product_category_slug not in ('saas','apm','ovp') 
GROUP BY 1,2 
ORDER BY 2,1 ASC 
) 

SELECT 
"Date", 
"Product Category", 
sum("Annual Spend") over (PARTITION BY "Product Category" ORDER BY "Date" ASC) "Annual Spend" 
FROM data 
ORDER BY 1, 2 ASC;

Query 10: Account Planning: Top Domains by Traffic

Required Licenses: Account Planning

SELECT 
applications.domain "Domain", 
applications.traffic "Intricately Traffic Value (ITV)", 
COUNT(name) "Number of Applications", 
COUNT(DISTINCT product_slug) "Number of Products" 
FROM intricately_product_deployment_applications applications 
WHERE company_slug = 'nike' 
GROUP BY "Domain", "Traffic" 
ORDER BY "Traffic" desc NULLS LAST 
LIMIT 10;

Query 11: Account Planning: Application-level Product Adoption

Required Licenses: Account Planning

SELECT 
applications.name "Application", 
products.product_name "Product", 
products.product_category "Product Category" 
FROM intricately_product_deployment_applications applications 
INNER JOIN intricately_product_deployments products ON products.product_slug = applications.product_slug 
WHERE applications.domain = 'nike.com' 
LIMIT 10;