How to combine 2 views /explores

[Michael_Lu]

View A: used devices (no primary key, many records per day)

• experiment
• bucket
• device
• seconds
• x
• y
• z
• day

Mandatory user-defined filter: experiment and the time interval.
Report the SUM(seconds) per bucket:

SELECT bucket, sum(seconds )
FROM A
WHERE experiment  = {user_filter} AND day BETWEEN  {user_filter}
GROUP BY bucket

In view A I have:

  measure: total_seconds {
    type: sum
    sql:${TABLE}.seconds ;;
  }

  measure: devices_count {
    sql: ${TABLE}.device_id ;;
    type: count_distinct
  }

View B: all devices , used and not-used

• experiment
• bucket
• device
• x
• y
• z
• day

For view B I report the # of distinct devices per bucket using the same user filter as above:

SELECT bucket, count(distinct device )
FROM B
WHERE experiment  = {user_filter} AND day BETWEEN  {user_filter}
GROUP BY bucket

I need to report the total seconds per all devices per bucket

SELECT A.bucket, 
sum(A.seconds ) / count(distinct B.device ) as avg_per_bucket,
100*count(distinct A.device ) / count(distinct B.device ) as usage_rate
FROM A join B  ON 
A.experiment=B.experiment
AND A.bucket = B.bucket
WHERE A.experiment  = {user_filter} AND A.day BETWEEN  {user_filter}
AND  B.experiment  = {user_filter} AND B.day BETWEEN  {user_filter}
GROUP BY A.bucket

I think what the approach which will try join every record in views A and B inside the explore A will not work because not for every record in A there is the matching record in B.

[Cyril_MTL_Analy]

Hello @Michael_Lu

A few options:

• If B has all the data from A, reverse the join for FROM B LEFT JOIN A
• If B and A have mutually excluded data, try creating a master table that will have both views buckets and experiments
• Try a UNION ALL instead of a join