Macro for parent details, with examples

companies.sql

-- 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

parent_details.sql

-- 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 %}