PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Struggle with sql counting

I need to calculate the (quarterly sessions conversion rate) = (number of orders) / (number of sessions) for an online store.

Here is the data table:

CREATE TABLE orders_full (
    order_id,
    sessions_id,
    created_time
)

CREATE TABLE sessions_data (
    -- records all the sessions (includes the one customers does not order anything)
    sessions_id,
    created_time.
)

I managed to get quarterly total number of orders by counting function. However, To get the conversion rate,I need to count all the sessions occurs each quarter.

Desired result:

year  quarter  number of orders  number of sessions  rate of conversion
2021     1            ...               ...                ...
2022     2            ...              ...                ...

I tried this code:

select 
    datepart(YEAR, orders_full.created_at)              as 'year',
    datepart(QUARTER,orders_full.created_at)            as 'quarter',
    count(orders_full.order_id)                                     as 'order created at this quarter',
    count(sessions_data.session_id)                                 as 'sessions created at this quarter'
'100*count(orders_full.order_id)/count(sessions_data.sesssion_id) as'conversion rate
from orders_full
right outer join sessions_data on sessions_

full.session_id

group by datepart(YEAR,orders_full.created_at), datepart(QUARTER,orders_full.created_at)
--having datepart(year, sessions_data.created_at) = datepart(year,orders_full.created_at) 
order by datepart(YEAR,orders_full.created_at), datepart(QUARTER,orders_full.created_at) ASC

It always give the conversions rate of 100% with same sessions and orders created each quarter.
is there any way to fix this issue?

Tags:

2

Answers


  1. 0

    You are counting rows in your selected set ; it doesn’t matter what column name you put in count() (they will be the same as long as session_id and order_id are not null). If you want to count the unique sessions, you can use:

    count(distinct sessions_data.session_id)

    If there is a chance that this can be zero (because some session_ids are null for some combinations of year & quarter, for example), then you can use something like this for your division:

    100*count(orders_full.order_id)/nullif(count(distinct sessions_data.sesssion_id),0)

    This avoids divide by zero errors by forcing the result to null.

    Also to consider is ‘integer division’; count returns an integer, so an integer divided by another integer, will not have decimals by default. if you want the result of the division to be accurate to some decimals, you can convert some operands to a ‘non-integer numeric’ data type (carefully considering the order and precedence of operators), like:

      100. 
* count(orders_full.order_id)
/ nullif(count(distinct sessions_data.sesssion_id),0)

    The decimal point in 100., converts the result of 100. * count(orders_full.order_id) to have 6 decimals.

    Login or Signup to reply.
  2. 0

    Hope this will works for you.

    WITH QuarterlyOrders AS (
    SELECT 
        DATEPART(YEAR, created_time) AS year,
        DATEPART(QUARTER, created_time) AS quarter,
        COUNT(order_id) AS number_of_orders
    FROM orders_full
    GROUP BY DATEPART(YEAR, created_time), DATEPART(QUARTER, created_time)
),

QuarterlySessions AS (
    SELECT 
        DATEPART(YEAR, created_time) AS year,
        DATEPART(QUARTER, created_time) AS quarter,
        COUNT(sessions_id) AS number_of_sessions
    FROM sessions_data
    GROUP BY DATEPART(YEAR, created_time), DATEPART(QUARTER, created_time)
)

SELECT 
    qOrders.year,
    qOrders.quarter,
    qOrders.number_of_orders,
    qSessions.number_of_sessions,
    100.0 * qOrders.number_of_orders / qSessions.number_of_sessions AS conversion_rate
FROM 
    QuarterlyOrders qOrders
    FULL OUTER JOIN QuarterlySessions qSessions
    ON qOrders.year = qSessions.year AND qOrders.quarter = qSessions.quarter
ORDER BY 
    qOrders.year, qOrders.quarter;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search