Created
April 20, 2018 03:52
-
-
Save clrcrl/dbb8ed848080aa3be5b6432df68205f6 to your computer and use it in GitHub Desktop.
Macro for parent details, with examples
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- This goes in the models/ directory | |
with companies as ( | |
select 1 as company_id, null as parent_company_id, 'Hooli' as name, 45 as revenue | |
union all | |
select 2, 1, 'HooliPhones', 84 | |
union all | |
select 3, 1, 'HooliChat', 32 | |
union all | |
select 4, 3, 'HooliMessage', 25 | |
union all | |
select 5, 4, 'HooliMoticons', 0 | |
union all | |
select 6, 5, 'HooliDooli', 8 | |
union all | |
select 7, 6, 'HooliCall', 4 | |
union all | |
select 8, null, 'Bachmanity', 0 | |
union all | |
select 9, 8, 'Not Hotdog', 0 | |
union all | |
select 10, 8, 'Pied Piper', 52 | |
union all | |
select 11, null, 'Raviga', 25 | |
union all | |
select 12, 11, 'Slack', 99 | |
union all | |
select 13, 12, 'Atlassian', 45 | |
union all | |
select 14, null, 'Aviato', 25 | |
) | |
, companies_with_top_parent_id as ( | |
{{parent_details( | |
table = "{{schema}}.companies", | |
entity_id_column_name = "company_id", | |
parent_id_column_name = "parent_company_id", | |
)}} | |
) | |
, top_parent_company_revenues as ( | |
select | |
companies_with_top_parent_id.top_parent_company_id | |
, count(companies.company_id) as number_of_companies -- note this includes the parent company itself | |
, sum(companies.revenue) as total_revenue | |
from companies | |
left join companies_with_top_parent_id on companies_with_top_parent_id.company_id = companies.company_id | |
group by 1 | |
) | |
select | |
* | |
from top_parent_company_revenues |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- this goes in the macros/ directory | |
{% macro parent_details(table, entity_id_column_name, parent_id_column_name, max_levels = 20) -%} | |
with entities as ( | |
select | |
* | |
from {{table}} | |
) | |
{% for i in range(max_levels) -%} | |
, level{{i}} as ( | |
select | |
{{i}} as level, | |
entities.{{entity_id_column_name}}, | |
entities.{{parent_id_column_name}}, | |
{% if loop.first -%} | |
entities.{{entity_id_column_name}} as top_{{parent_id_column_name}} | |
{% else -%} | |
parent_entities.top_{{parent_id_column_name}} | |
{%- endif %} | |
from entities | |
{% if loop.first -%} | |
where {{parent_id_column_name}} is null | |
{% else -%} | |
inner join level{{i-1}} as parent_entities on entities.{{parent_id_column_name}} = parent_entities.{{entity_id_column_name}} | |
{% endif %} | |
) | |
{%- endfor %} | |
{% for i in range(max_levels) -%} | |
select * | |
from level{{i}} | |
{% if not loop.last %} | |
union all | |
{% endif %} | |
{% endfor %} | |
{%- endmacro %} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment