How to write SQL query without join?

user_4708632 0
Viewed 96

Recently during an interview I was asked a question: if I have a table like as below:

enter image description here

The requirement is: how many orders and how many shipments per day (based on date column) - output needs to be like this:

enter image description here

I have written the following code, but interviewer ask me to write a SQL query without JOIN and UNION, achieve the same output.

SELECT 
    COALESCE(a.order_date, b.ship_date), orders, shipments 
FROM
    (SELECT 
         order_date, COUNT(1) AS orders 
     FROM 
         table 
     GROUP BY 1) a
FULL JOIN 
    (SELECT
         ship_date, COUNT(1) AS shipments 
     FROM table) b ON a.order_date = b.ship_date

Is this possible? Could you guys please advice?

google-bigquery sql
2 Answers

You can use UNION and GROUP BY with conditional aggregation as follows:

SELECT DATE_, 
       COUNT(CASE WHEN FLAG = 'ORDER' THEN 1 END) AS ORDERS,
       COUNT(CASE WHEN FLAG = 'SHIP' THEN 1 END) AS SHIPMENTS
 FROM (SELECT ORDER_DATE AS DATE_, 'ORDER' AS FLAG FROM YOUR_TABLE
       UNION ALL
       SELECT SHIP_DATE AS DATE_, 'SHIP' AS FLAG FROM YOUR_TABLE) T
user_115656290

In BigQuery, I would express this as:

select date, countif(n = 0) as orders, countif(n = 1) as numships
from t cross join
     unnest(array[order_date, ship_date]) date with offset n
group by 1
order by date;

The advantage of this approach (over union all) is two-fold. First, it only scans the table once. More importantly, the unnest() is all on the same node where the data resides -- so data does not need to be moved for the unpivot.

user_11440350
Related