How do I calculate the percentage of dollar amount of approved orders out of total dollar amount of orders placed?

user_11773948 0
Viewed 120

I have two tables, orders and records as below.

Orders table has three columns:

order_id     seller     order_price
  123         abc           50 
  456         abc           75
  789         def           60

Records table also has three columns, where it records any changes made to the order:

order_id       log_made_at          record
  123       2018-08-05 11:00:00     order approved
  123       2018-08-05 12:00:00     shipping city updated
  123       2018-08-05 12:00:10     order declined
  456       2018-08-05 12:10:00     order approved
  789       2018-08-05 12:20:10     order declined

The 'record' column stores string values such as 'order approved', 'order declined', 'shipping city updated', etc. The 'log_made_at' column stores the timestamp of when the records were made.

If I wanted to calculate the percentage of dollar amount of approved orders out of total dollar amount of orders placed ($ amount of approved orders) / ($ amount of total orders placed) per seller, what query should I use? I'm having difficulty isolating orders where the last record made was 'order approved' to use their prices as $ amount of approved orders.

The result I want using the sample data above would look something like this:

seller     approved_order_dollar_amount_percentage
  abc                   0.6
  def                   0.0

How I got the above calculations: seller abc has two orders(123 and 456) that each has a price tag of 50 and 75, so his total order price is 125. However, only order 456 has a final log of 'order approved' made since order 123 was ultimately declined. So seller abc's final approved order dollar amount percentage is 75/125 = 0.6.

The query I tried running is this one, but it keeps giving me the wrong numbers:

SELECT order_price
        FROM orders o
        INNER JOIN records r ON o.order_id = r.order_id
        WHERE r.log_made_at IN (
            SELECT MAX(log_made_at) 
            FROM records
            GROUP BY order_id)
        AND r.record = 'order approved'
    )/SUM(total_order_price) AS approval_rate_by_sum
greatest-n-per-group sum mysql average sql
2 Answers

You could use conditional aggregation:

select 
    seller, 
    sum(case when r.record = 'order approved' then o.order_price else 0 end) 
        / sum(o.order_price) as approved_order_dollar_amount_percentage
from orders o
left join (
    select r.*, row_number() over(partition by orderid order by log_made_at desc) rn
    from records r
) r on r.order_id = o.order_id and r.rn = 1
group by seller

The subquery retrieves the last record per order, then the outer query aggregate and does the computation.

user_106767160

First, sum the orders approved by the seller and after use the result as a table to calc percent of approved orders by the total orders.

select t.seller, 
      (t.orders_approved / (select sum(o.order_price) 
                            from orders o 
                            where o.seller=t.seller)
      ) approved_order_dollar_amount_percentage
from
  (select o.seller, sum(o.order_price) orders_approved
   from records r inner join orders on (o.order_id=r.order_id)
   where
    r.record = 'order approved'
    and
    r.log_mad_at = (
       select max(r2.log_mad_at)
       from records r2
       where 
        r2.order_id=r.order_id
      )
   group by o.seller
  ) t
user_53314010
Related