Cross joining two tables with "using" instead of "on"

Question

I found a SQL query in a book which i am not able to understand. From what i understand there are two tables - date which has a date_id and test_Date column, the 2nd table has date_id and obs_cnt.

select t1.test_date 
      ,sum(t2.obs_cnt)
from date t1
cross join
     (transactions join date using (date_id)) as t2
where t1.test_date>=t2.test_date
group by t1.test_date
order by t1.test_date

Can someone help me understand what this code does or how the output will look like.

I understand obs_cnt variable is being aggregated at a test_date level.

I understand the use of using in placed on on. But what i dont get is how the date table is being reference twice, does it mean it is being joined twice?

@Rup Ya its test_date. Edited the question to reflect the change — Raj, Jul 26 '18 at 23:14

Rup · Accepted Answer · 2018-07-27T09:56:38.527

But what i dont get is how the date table is being reference twice, does it mean it is being joined twice?

Yes it is, although it's probably easier to think of t2 as a whole rather than as a function of the date table: t2 is the transaction table but with the actual date representation of the test_date rather than an ID.

I assume there's actually some context for all of this in the book, but it looks like this will produce:

one row of output for every row in the date table (t1), in order of test_date
for each row, total up the number of observations for all transactions that happened on or before that date, using our transactions-with-date table t2.

I understand obs_cnt variable is being aggregated at a test_date level.

It's being aggregated against t1 test_date, which is the constraint we're using to select the rows in t2 that are summed.

small change, the aggregation for each row would be the sum of number of observations that happened on or before that date — Raj, Jul 27 '18 at 00:24

Cross joining two tables with "using" instead of "on"

1 Answers1