How to join and filter between 3 tables with ecto query expressions?

query = from u in User, 
        join: a in assoc(u, :api_keys), 
        where: u.email == ^email,
        where: a.is_active == true,
        select: {u, a}
user = Repo.one(query) |> Enum.map(fn ({u, a}) -> %{u | api_keys: a} end)

api_key_query = from a in ApiKey, where: a.is_active == true
query = from u in User, 
        join: a in assoc(u, :api_keys), 
        where: u.email == ^email,
        preload: [api_keys: ^api_key_query]
user = Repo.one(query)

declare @startDate date
declare @endDate date

select @startDate = '2016-12-28'
select @endDate = '2016-12-31'

-- Recursive CTE to generate dates between start and end dates 
;with dateRange as
(
  select @startDate as dt  
  union all
  select dateadd(dd, 1, dt)
  from dateRange
  where dateadd(dd, 1, dt) < +dateadd(dd, 1, @endDate)
)
select  
  tbl_master.dt as ReservedDate,
  tbl_master.TableType,
  tbl_master.Shared,
  case tbl_master.shared 
    when 'FULL' then 
        tbl_master.avail+coalesce(tbl_shared.shared_count,0)
    when 'SHARED' then
       tbl_master.avail-coalesce(tbl_shared.shared_count,0)
    else 
       tbl_master.avail
   end as Totalavail

from (
  -- Join date range data with `TableMaster` to get table availablity
  select dateRange.dt,TableMaster.TableType,TableMaster.Shared,count(*) as avail
  from dateRange left join TableMaster on 1=1
  group by dateRange.dt,TableMaster.TableType,TableMaster.Shared
) as tbl_master

-- Join `TableSharedDetails` for adding and subtracting counts from table availability   
left join (
  select ReservedDate,TableType,count(*) as shared_count 
  from TableSharedDetails
  group by ReservedDate,TableType
) tbl_shared on 
tbl_master.dt=tbl_shared.ReservedDate
and tbl_master.TableType=tbl_shared.TableType;

from hearing in "hearings",
  join: hcl in "hearing_category_link", on: hcl.hearing_id == hearing.id,
  join: category in "categories", on: hcl.category_id == category.id

user_id = 2

subquery = from(s in Message,
                select: %{ sender_id: s.sender_id, max_time: max(s.inserted_at) },
                where: s.receiver_id == ^user_id,
                group_by: s.sender_id
               )

query = from(m in Message,
             join: s in subquery(subquery), on: m.inserted_at == s.max_time and m.sender_id == s.sender_id,
             where: m.receiver_id == ^user_id
            )

Repo.all(query)

 > Ecto.Adapters.SQL.to_sql(:all, Repo, query)
 {"SELECT m0.\"id\", m0.\"sender_id\", m0.\"receiver_id\", m0.\"body\", m0.\"received_at\", m0.\"inserted_at\" FROM \"messages\" AS m0 INNER JOIN (SELECT m0.\"sender_id\" AS \"sender_id\", max(m0.\"inserted_at\") AS \"max_time\" FROM \"messages\" AS m0 WHERE (m0.\"receiver_id\" = $1) GROUP BY m0.\"sender_id\") AS s1 ON (m0.\"inserted_at\" = s1.\"max_time\") AND (m0.\"sender_id\" = s1.\"sender_id\") WHERE (m0.\"receiver_id\" = $2)", [30064, 30064]}