Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Opportunity Chasm
{
"cells": [
{
"cell_type": "code",
"execution_count": 6,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"from IPython.display import IFrame"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```sql\n",
"/*\n",
"create a single row of usage data for a single account.\n",
"*/\n",
"\n",
"create or replace table public.usage\n",
" as\n",
" select 1 as id\n",
" , 111 as account_id\n",
" , '2016-11-01'::date as created_date\n",
" , 300 as usage;\n",
"```"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```sql\n",
"/*\n",
"create a single account.\n",
"*/\n",
"\n",
"create or replace table public.account\n",
" as\n",
" select 111 as id\n",
" , 'a' as salesforce_id;\n",
"```"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```sql\n",
"/*\n",
"create a single salesforce account.\n",
"*/\n",
"\n",
"create or replace table public.salesforce_account\n",
" as\n",
" select 'a' as id\n",
" , 'Acme, Co.' as name;\n",
"```"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```sql\n",
"/*\n",
"create two opportunities whose subscription boundaries\n",
"are mutually exclusive. this should be the standard case.\n",
"*/\n",
"\n",
"create or replace table public.salesforce_opportunity\n",
" as\n",
" select 'x' as id\n",
" , 'a' as account_id\n",
" , '2015-12-01'::date as created_date\n",
" , '2015-12-01'::date as subscription_start_date\n",
" , '2016-12-01'::date as subscription_end_date\n",
" , 2 as price_per_credit\n",
" union all\n",
" select 'x' as id\n",
" , 'a' as account_id\n",
" , '2016-12-01'::date as created_date\n",
" , '2016-12-02'::date as subscription_start_date\n",
" , '2017-12-02'::date as subscription_end_date\n",
" , 1.5 as price_per_credit;\n",
"```"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```sql\n",
"/*\n",
"we can safely map snowflake usage to a salesforce account,\n",
"as an account is a fairly high-level entity. We still only get one\n",
"row returned, we just have more attributes about the salesforce account.\n",
"*/\n",
"\n",
"select *\n",
"from public.usage\n",
"left join public.account\n",
"on usage.account_id = account.id\n",
"left join public.salesforce_account\n",
"on account.salesforce_id = salesforce_account.id;\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"\n",
" <iframe\n",
" width=\"900\"\n",
" height=\"350\"\n",
" src=\"https://snowflake.looker.com/embed/explore/sql__dqrg26bgscgtjp/sql_runner_query?qid=fSeE2mxENvqwcoaLge2Av7&toggle=pik\"\n",
" frameborder=\"0\"\n",
" allowfullscreen\n",
" ></iframe>\n",
" "
],
"text/plain": [
"<IPython.lib.display.IFrame at 0x106c5bda0>"
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"IFrame('https://snowflake.looker.com/embed/explore/sql__dqrg26bgscgtjp/sql_runner_query?qid=fSeE2mxENvqwcoaLge2Av7&toggle=pik', 900, 350)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```sql\n",
"/*\n",
"if we naively join usage to salesforce_opportunity, first through\n",
"account then salesforce_account, we get a fanout.\n",
"*/\n",
"\n",
"select *\n",
"from public.usage\n",
"left join public.account\n",
"on usage.account_id = account.id\n",
"left join public.salesforce_opportunity\n",
"on account.salesforce_id = salesforce_opportunity.account_id;\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"\n",
" <iframe\n",
" width=\"900\"\n",
" height=\"350\"\n",
" src=\"https://snowflake.looker.com/embed/explore/sql__f3c3ndmsfgqwxf/sql_runner_query?qid=ZKNMJXhZsCWi2G2aHWcS1O&toggle=pik\"\n",
" frameborder=\"0\"\n",
" allowfullscreen\n",
" ></iframe>\n",
" "
],
"text/plain": [
"<IPython.lib.display.IFrame at 0x106c5b908>"
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"IFrame('https://snowflake.looker.com/embed/explore/sql__f3c3ndmsfgqwxf/sql_runner_query?qid=ZKNMJXhZsCWi2G2aHWcS1O&toggle=pik', 900, 350)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```sql\n",
"/*\n",
"our only reasonable option is to join first through\n",
"account then salesforce_account then to salesforce_opportunity on\n",
"the salesforce account_id as well as the usage date being between\n",
"our subscription boundaries. this query yields a single row, mapping to our\n",
"first opportunity. success.\n",
"*/\n",
"\n",
"select *\n",
"from public.usage\n",
"left join public.account\n",
"on usage.account_id = account.id\n",
"left join public.salesforce_opportunity\n",
"on account.salesforce_id = salesforce_opportunity.account_id\n",
"and usage.created_date between salesforce_opportunity.subscription_start_date and salesforce_opportunity.subscription_end_date;\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"\n",
" <iframe\n",
" width=\"900\"\n",
" height=\"350\"\n",
" src=\"https://snowflake.looker.com/embed/explore/sql__q4dtkz3725xncz/sql_runner_query?qid=ruU68mVaFs6jwzxQy1aZC8&toggle=pik\"\n",
" frameborder=\"0\"\n",
" allowfullscreen\n",
" ></iframe>\n",
" "
],
"text/plain": [
"<IPython.lib.display.IFrame at 0x106ccf9e8>"
]
},
"execution_count": 12,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"IFrame('https://snowflake.looker.com/embed/explore/sql__q4dtkz3725xncz/sql_runner_query?qid=ruU68mVaFs6jwzxQy1aZC8&toggle=pik', 900, 350)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```sql\n",
"/*\n",
"create two opportunities that were concurrently active\n",
"between 2016-11-01 and 2016-12-01\n",
"*/\n",
"\n",
"create or replace table public.salesforce_opportunity\n",
" as\n",
" select 'x' as id\n",
" , 'a' as account_id\n",
" , '2015-12-01'::date as created_date\n",
" , '2015-12-01'::date as subscription_start_date\n",
" , '2016-12-01'::date as subscription_end_date\n",
" , 2 as price_per_credit\n",
" union all\n",
" select 'x' as id\n",
" , 'a' as account_id\n",
" , '2016-10-31'::date as created_date\n",
" , '2016-11-01'::date as subscription_start_date\n",
" , '2017-04-01'::date as subscription_end_date\n",
" , 1.5 as price_per_credit;\n",
"```"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```sql\n",
"/*\n",
"our previously successful pattern fails. we get two rows in our result set. \n",
"this is because our usage record thinks it applies to both opportunities.\n",
"*/\n",
"\n",
"select *\n",
"from public.usage\n",
"left join public.account\n",
"on usage.account_id = account.id\n",
"left join public.salesforce_opportunity\n",
"on account.salesforce_id = salesforce_opportunity.account_id\n",
"and usage.created_date between salesforce_opportunity.subscription_start_date and salesforce_opportunity.subscription_end_date;\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"\n",
" <iframe\n",
" width=\"900\"\n",
" height=\"350\"\n",
" src=\"https://snowflake.looker.com/embed/explore/sql__mtscdhqqbgvyfg/sql_runner_query?qid=Il2d30rjV45j51voK3WtdD&toggle=pik\"\n",
" frameborder=\"0\"\n",
" allowfullscreen\n",
" ></iframe>\n",
" "
],
"text/plain": [
"<IPython.lib.display.IFrame at 0x106ccfa90>"
]
},
"execution_count": 11,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"IFrame('https://snowflake.looker.com/embed/explore/sql__mtscdhqqbgvyfg/sql_runner_query?qid=Il2d30rjV45j51voK3WtdD&toggle=pik', 900, 350)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.5.2"
},
"latex_envs": {
"bibliofile": "biblio.bib",
"cite_by": "apalike",
"current_citInitial": 1,
"eqLabelWithNumbers": true,
"eqNumInitial": 0
}
},
"nbformat": 4,
"nbformat_minor": 0
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.