SQL: How to join a table and then create new columns by filtering on the same 'DATE' field but with different criteria

I have to create a SQL query that shows the active users within the last 90 days, users that are not active, and then the percentage of inactive users (inactive/all users). I have two different tables: orders (order_id, customer_id, order_date, total) and order_items (order_id, product_id, quantity, unit_price).

I was able to create the first fields (active and not active), but separately.

I tried to combine both queries but it resulted in repeats of the entries.

Also, still not sure how to even get that percentage field since I've only been able to get active and inactive separately.

 SELECT order_items.product_id, COUNT(orders.customer_id) AS active_cust
    FROM orders
    JOIN order_items
    ON orders.order_id = order_items.order_id
    WHERE orders.order_date >= CURRENT_DATE - INTERVAL '90 day'
    GROUP BY order_items.product_id;

SELECT order_items.product_id, COUNT(orders.customer_id) AS inactive_cust
    FROM orders
    JOIN order_items
    ON orders.order_id = order_items.order_id
    WHERE orders.order_date <= CURRENT_DATE - INTERVAL '90 day'
    GROUP BY order_items.product_id;

SELECT * FROM
    (SELECT order_items.product_id, COUNT(orders.customer_id) AS active_cust
    FROM orders
    JOIN order_items
    ON orders.order_id = order_items.order_id
    WHERE orders.order_date >= CURRENT_DATE - INTERVAL '90 day'
    GROUP BY order_items.product_id
    ) AS q1,
    (SELECT COUNT(orders.customer_id) AS inactive_cust
    FROM orders
    JOIN order_items
    ON orders.order_id = order_items.order_id
    WHERE orders.order_date <= CURRENT_DATE - INTERVAL '90 day'
    GROUP BY order_items.product_id
    ) AS q2
    GROUP BY product_id, q1.active_cust, q2.inactive_cust
    ORDER BY product_id;

Solution

The trick you need is a filtered aggregate, for example:

SELECT order_items.product_id,
       COUNT(orders.customer_id) FILTER (WHERE orders.order_date >= CURRENT_DATE - 90) AS active_cust,
       COUNT(orders.customer_id) FILTER (WHERE orders.order_date < CURRENT_DATE - 90) AS inactive_cust
FROM orders
   JOIN order_items
      ON orders.order_id = order_items.order_id
GROUP BY order_items.product_id;

Note that the same customer_id could occur several times because of the inner join. If you want to count distinct customer_ids only, use COUNT(DISTINCT ...).