join views by running agreggations in each view separately before final join

[petr_p]

Hello, I would love to speed up some looker dashboards by updating our data models. Here is how data looks like in druid:

`date, dim1, dim2, metric1, metric2.`

I can not change how this data looks like in druid, but I might be able to create druid views.

Many of my looker metrics look like this:

`sql: sum(case when dim1 == 'dim1_filter_value' then metric1 end) as looker_metric_1`

with just a handful of values like this dim1_filter_value used in looker views now.

So what I want to do is Looker explore to join two views (or maybe join two explores?) so each view works like CTE that has aggregation inside. This way instead of aggregating post join, I can do aggregations on smaller volumes of data and do join post aggregation. Test of such approach in case of our data speed things up 3-10x.

So I want the following druid SQL generated by Looker:

```

with cte1 as (select * from data where dim1 = 'dim1_filter_value)

, cte2 as (select * from data where dim2 = "dim2_filter_value)

, results1 as (select date, metric1 from cte1 group by date)

, result2 as (select date, metric2 from cte2 group by date)

select r1.date, r1.metric1, r2.metric2 from results1 as r1

join results2 as r2

on r1.date=r2.date

```

So far using joins of Looker views in Looker explore (Model) I get similar code, but group by is applied only to final results, and not applied inside the result1 and result2 CTEs, and that makes computation much slower than keeping all in one view and one explore. Please advice how I can tell Looker to build these `result1` and `result2` CTEs with group by inside of them. I tried `primary_key: yes` in dimensions inside views but that does not help.

[petr_p]

Another approach is to have something in Looker to automatically add filters as a superset of selected metrics. Maybe this can be done at least for 1 dimension? Something that will add `where dim1 = 'dim1_filter_value` if metric1 was selected and if both metric1 and metric2 selected I wish Looker can add `where dim1='dim1_filter_value' OR dim2='dim2_filter_value'`

[petr_p]

or maybe I can try liquid to dynamically generate derived table from selected measures? https://cloud.google.com/looker/docs/liquid-variable-reference