SQL query optimization for derived tables

Looker Q&A Looker
1

I’m trying to run SQL RANK() OVER function on top of my existing table to remove duplicated rows of BigQuery table.

Looks like I can do it only using the derived table like

derived_table: {
sql:
SELECT
*,
rank() over(partition by field1, field2, field3 order by timestamp desc) as rank
FROM myTable
;;
}


The problem is that Looker will always run this query before querying the required data for the widget which takes a lot of time in a column-based DB.

Is there a way to somehow optimise the derived table or the query to avoid double querying in Looker widgets?

So instead of:

SELECT field1, field2 FROM (
SELECT
*,
rank() over(partition by field1, field2, field3 order by timestamp desc) as rank
FROM myTable
) WHERE rank = 1


I’d get something like:

SELECT
field1,
field2,
rank() over … as rank
FROM myTable


?