demo_segment/_A_alias_mapping.view.lkml at master · OffgridElectric/demo_segment · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
view: page_aliases_mapping {
  derived_table: {
    sql: with

      -- Establish all child-to-parent edges from tables (tracks, pages, aliases)
      all_mappings as (
        select anonymous_id
        , user_id
        , received_at as received_at
        from zola_sales.tracks

        union

        select user_id
          , null
          , received_at
        from zola_sales.tracks

        union

        select anonymous_id
          , user_id
          , received_at
        from zola_sales.pages

        union

        select user_id
        , null
        , received_at
        from zola_sales.pages
      )

      select
                  distinct anonymous_id as alias
                  , coalesce(first_value(user_id ignore nulls)
                  over(
                    partition by anonymous_id
                    order by received_at
                    rows between unbounded preceding and unbounded following),anonymous_id) as looker_visitor_id
      from all_mappings
       ;;
  }

  # Anonymous ID
  dimension: alias {
    primary_key: yes
    sql: ${TABLE}.alias ;;
  }

  # User ID
  dimension: looker_visitor_id {
    sql: ${TABLE}.looker_visitor_id ;;
  }

  measure: count {
    type: count
  }

  measure: count_visitor {
    type: count_distinct
    sql: ${looker_visitor_id} ;;
  }
}

### More Complex Aliasing Using Alias Table ###

#     sql: |
#        with
#
#             -- Establish all child-to-parent edges from tables (tracks, pages, aliases)
#             all_mappings as (
#               select anonymous_id
#                 , user_id
#                 , received_at
#               from hoodie.tracks
#
#               union
#
#               select user_id
#                 , null
#                 , received_at
#               from hoodie.tracks
#
#                union
#
#                select previous_id
#                 , user_id
#                 , received_at
#                from hoodie.aliases
#
#                union
#
#                select user_id
#                  , null
#                  , received_at
#                from hoodie.aliases
#
#                union
#
#                select anonymous_id
#                   , user_id
#                   , received_at
#                from hoodie.pages
#
#                union
#
#                select user_id
#                   , null
#                   , received_at
#                from hoodie.pages
#             ),
#
#             -- Only keep the oldest non-null parent for each child
#             realiases as (
#               select distinct alias
#                 , first_value(next_alias ignore nulls) over(partition by alias order by realiased_at rows between unbounded preceding and unbounded following) as next_alias
#               from all_mappings
#             )
#
#             -- Traverse the tree upwards and point every node at its root
#             select distinct r0.alias
#               , coalesce(r9.next_alias
#                   , r9.alias
#                   , r8.alias
#                   , r7.alias
#                   , r6.alias
#                   , r5.alias
#                   , r4.alias
#                   , r3.alias
#                   , r2.alias
#                   , r1.alias
#                   , r0.alias
#                 ) as looker_visitor_id
#             from realiases r0
#               left join realiases r1 on r0.next_alias = r1.alias
#               left join realiases r2 on r1.next_alias = r2.alias
#               left join realiases r3 on r2.next_alias = r3.alias
#               left join realiases r4 on r3.next_alias = r4.alias
#               left join realiases r5 on r4.next_alias = r5.alias
#               left join realiases r6 on r5.next_alias = r6.alias
#               left join realiases r7 on r6.next_alias = r7.alias
#               left join realiases r8 on r7.next_alias = r8.alias
#               left join realiases r9 on r8.next_alias = r9.alias
#