Trying to make a new table by pulling data from two tables and keep getting 'Error: Every derived table must have its own alias' on this query

I have an 'Orders' table and a 'Records' table.

Orders table has the following columns:

order_id       order_date        seller        order_price

Records table has the following columns:

order_id        record_created_at         record_log

I'm trying to pull and compile the following list of data but I keep getting an error message:

order_week
seller
total_num_orders
under100_count --this is the number of orders that were < $100
over100_count --this is the number of order that >= $100
approved --this is the number of orders that were approved by the payment platform

Here's my query:

SELECT order_week, seller, total_num_orders, under100_count, over100_count, approved
FROM (

    SELECT 
        EXTRACT(WEEK FROM order_created_at) AS order_week,
        merchant_name AS seller, 
        COUNT(merchant_name) AS total_num_orders,
        SUM(DISTINCT total_order_price < 100) AS under100_count,
        SUM(DISTINCT total_order_price >= 100) AS over100_count
    FROM orders o
    GROUP BY order_week, seller)

INNER JOIN (

    SELECT
        COUNT(DISTINCT o.order_id) AS approved
    FROM records r
    WHERE record_log = 'order approved'
    GROUP BY order_id)

ON l.order_id = o.order_id;

What am I doing wrong?

Solution

The subquery in the join needs an alias. It also needs to return the order_id column, so it can be joined.

inner join ( select order_id, ... from records ... group by order_id) r  --> here
on l.order_id = o.order_id

I would actually write your query as:

select 
    extract(week from o.order_created_at) as order_week,
    o.merchant_name as seller, 
    count(*) as total_num_orders,
    sum(o.total_order_price < 100) as under100_count,
    sum(o.total_order_price >= 100) as over100_count,
    sum(r.approved) approved
from orders o
inner join (
    select order_id, count(*) approved
    from records r
    where record_log = 'order approved'
    group by order_id
) r on r.order_id = o.order_id;
group by order_week, seller, approved

Rationale:

you don't want, and need, distinct in the aggregate functions here; it is inefficient, and might even yield wrong results
count(*) is more efficient count(<expression>) - so, use it, unless you know why you are doing otherwise
I removed an unecessary level of nesting

If there are orders without records, you might want a left join instead.