Full Outer Join Primary Key Issue

[ivancans]

Hi,

I would appreciate any help related to the following situation. 

I have 3 views:

• Users - Unique table of users
• Orders - All the orders users have
• Service Purchases - All the subscriptions users have

The model starts with orders to which users is joined via full outer join to which service purchases are joined via regular left join.

join: user {
relationship: many_to_one
type: full_outer
sql_on: ${order.user_id} = ${user.id} ;;
}

join: service_purchase {
relationship: one_to_many
sql_on: ${user.id} = ${service_purchase.user_id} ;;
}

There are 2 reasons why I need to user full outer join. 1.The starting point of the model is order view; 2.Not all users who have orders also have service purchases and vice versa.

So far so good, but the issue starts when I need to create a measure that combines $ amount from orders and $ amount from service purchases. Looker allows that but it can't deduplicate the results based on primary keys because by default, it uses the primary key from the view that measure is built in (service purchases in this case) and using sql_distincrt_key from orders also doesn't work. Why? Well, as we know, full outer join in the rows where table B doesn't match with table A, return nulls, including primary key columns. So, the result is that, when I try to user this measure that combines both views connected through full outer join, there are repeating ID's when they are present, and nulls for IDs when there is no match.

I tried using row_number window function to create artificial primary key for each view and use it in a coalesce when tables don't match and rows in either A or B table return nulls but that doesn't work because deduplication logic is used within a sum function and because deduplication logic uses my window function, it can't be done because it isn't allowed to use window functions within aggregate functions.

So this is the issue. Please help 🙂