Liquid in SQL statement for derived table

[Dawid]

I am just wondering if this is possible:

view: finance_services {
  derived_table: {
    sql:
       {% if parameter.locale == "all" %}
          SELECT different statement FROM table
       {% else %}
          SELECT statement FROM table
       {% endif %}
    ;;
  }

The reason for it is that this query has some ranking based on a grouping but I want to be able to do calculations for totals as well. I want to avoid putting liquid in 10 different places in the query (select fields, group by, joins, window functions) and would prefer to have the whole query as conditional.

Is it possible? Is it even the best way to approach it?

[michael_zearn · Accepted solution]

Yes, this is possible! We do something similar for query speed purposes with our time-series data, where we select which summary table to pull from based on how the user is grouping the data:

view: usage_distributions_small {
  derived_table: {
    sql: SELECT * FROM
            {% if select_date_window._parameter_value == "'week'" %} analytics.daily_usage_summary_week
            {% elsif select_date_window._parameter_value == "'month'" %} analytics.daily_usage_summary_month
            {% elsif select_date_window._parameter_value == "'year'" %} analytics.daily_usage_summary_year
            {% elsif select_date_window._parameter_value == "'school_year'" %} analytics.daily_usage_summary_school_year
            {% else %} analytics.daily_usage_summary_school_year
            {% endif %} base

            WHERE {% condition select_date_range %}
              {% if select_date_window._parameter_value == "'school_year'" or select_date_window._parameter_value == "'year'"  %} base.min_daydate
              {% else %} base.date_window
              {% endif %}
            {% endcondition %}
              ;;
  }
  # dimensions and measures go here
}

We’re not doing any separate grouping/joins/etc in this example, but there are other places we do that (the code would just be too big to fit here).

These are the biggest things we’ve had to think about:

1. It can be a good way to approach this problem unless there is a ton of code you’ll have to copy/paste between each case and/or a lot of cases. If so, just putting liquid everywhere may be cleaner, or Liquid’s variables or loops might be useful.
2. We make sure each query returns the same columns since the user will see the same dimensions/metrics in Looker regardless. Or if it doesn’t, it should be obvious to the user why some dimensions/metrics return errors some of the time.