Lecture 112, how to verify the result in mysql


#1

The final hive query in lecture 112 is:
select * from (
select o.order_id, date_format(o.order_date, ‘YYYYMMDD’) order_date, o.order_status, oi.order_item_subtotal,
round(sum(oi.order_item_subtotal) over (partition by o.order_id), 2) order_revenue,
round(oi.order_item_subtotal/round(sum(oi.order_item_subtotal) over (partition by o.order_id), 2),2) pct_revenue,
round(avg(oi.order_item_subtotal) over (partition by o.order_id), 2) avg_revenue
from orders o join order_items oi
on o.order_id = oi.order_item_order_id
where o.order_status in (‘COMPLETE’, ‘CLOSED’)) q
where order_revenue >= 1000 o.order_id = 57779
order by order_date, order_revenue desc limit 20;

which produces result below:
order_id order_date order_status order_item_subtotal order_revenue pct_revenue avg_revenue
57779 2013-07-25 COMPLETE 149.94 1649.8 0.09 329.96
57779 2013-07-25 COMPLETE 399.98 1649.8 0.24 329.96
57779 2013-07-25 COMPLETE 299.98 1649.8 0.18 329.96
57779 2013-07-25 COMPLETE 499.95 1649.8 0.30 329.96
57779 2013-07-25 COMPLETE 299.95 1649.8 0.18 329.96

Now I would like to verify the result in mysql, here is my query:

select o.order_id, date_format(o.order_date, ‘%Y-%m-%d’) order_date, o.order_status,
sum(oi.order_item_subtotal) order_revenue,
oi.order_item_subtotal/sum(oi.order_item_subtotal) pct_revenue,
sum(oi.order_item_subtotal) / count(1) avg_revenue
from order_items oi
inner join orders o on o.order_id = oi.order_item_order_id
where o.order_id = 57779
group by order_id, order_item_subtotal;

The script produces the following result:

| order_id | order_date | order_status | order_revenue | pct_revenue | avg_revenue |
| 57779 | 2013-07-25 | COMPLETE | 149.94000244140625 | 1 | 149.94000244140625 |
| 57779 | 2013-07-25 | COMPLETE | 299.95001220703125 | 1 | 299.95001220703125 |
| 57779 | 2013-07-25 | COMPLETE | 299.9800109863281 | 1 | 299.9800109863281 |
| 57779 | 2013-07-25 | COMPLETE | 399.9800109863281 | 1 | 399.9800109863281 |
| 57779 | 2013-07-25 | COMPLETE | 499.95001220703125 | 1 | 499.95001220703125 |

Can anyone help to fix the query? the pct_revenue and avg_revenue are not correct.

Thank you very much.