{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "## Ranking and Filtering - Recap\n", "\n", "Let us recap the procedure to get top 5 products by revenue for each day." ] }, { "cell_type": "code", "execution_count": 19, "metadata": { "tags": [ "remove-cell" ] }, "outputs": [ { "data": { "text/html": [ "\n" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%%HTML\n", "" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "* We have our original data in **orders** and **order_items**\n", "* We can pre-compute the data or create a view with the logic to generate **daily product revenue**\n", "* Then, we have to use the view or table or even sub query to compute rank\n", "* Once the ranks are computed, we need to use sub query to filter based up on our requirement.\n", "\n", "Let us come up with the query to compute daily product revenue." ] }, { "cell_type": "code", "execution_count": 21, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "The sql extension is already loaded. To reload it, use:\n", " %reload_ext sql\n" ] } ], "source": [ "%load_ext sql" ] }, { "cell_type": "code", "execution_count": 22, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db\n" ] } ], "source": [ "%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db" ] }, { "cell_type": "code", "execution_count": 23, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "30 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
order_dateorder_item_product_idrevenue
2013-07-25 00:00:0010045599.72
2013-07-25 00:00:001915099.49
2013-07-25 00:00:009574499.70
2013-07-25 00:00:003653359.44
2013-07-25 00:00:0010732999.85
2013-07-25 00:00:0010142798.88
2013-07-25 00:00:004031949.85
2013-07-25 00:00:005021650.00
2013-07-25 00:00:006271079.73
2013-07-25 00:00:00226599.99
2013-07-25 00:00:0024319.96
2013-07-25 00:00:00821207.96
2013-07-25 00:00:00625199.99
2013-07-25 00:00:00705119.99
2013-07-25 00:00:00572119.97
2013-07-25 00:00:00666109.99
2013-07-25 00:00:00725108.00
2013-07-25 00:00:00134100.00
2013-07-25 00:00:0090699.96
2013-07-25 00:00:0082895.97
2013-07-25 00:00:0081079.96
2013-07-25 00:00:0092479.95
2013-07-25 00:00:0092679.95
2013-07-25 00:00:009374.97
2013-07-25 00:00:0083563.98
2013-07-25 00:00:0089749.98
2013-07-26 00:00:00100410799.46
2013-07-26 00:00:003657978.67
2013-07-26 00:00:009576899.54
2013-07-26 00:00:001916799.32
" ], "text/plain": [ "[(datetime.datetime(2013, 7, 25, 0, 0), 1004, Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 191, Decimal('5099.49')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 957, Decimal('4499.70')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 365, Decimal('3359.44')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 1073, Decimal('2999.85')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 1014, Decimal('2798.88')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 403, Decimal('1949.85')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 502, Decimal('1650.00')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 627, Decimal('1079.73')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 226, Decimal('599.99')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 24, Decimal('319.96')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 821, Decimal('207.96')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 625, Decimal('199.99')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 705, Decimal('119.99')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 572, Decimal('119.97')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 666, Decimal('109.99')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 725, Decimal('108.00')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 134, Decimal('100.00')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 906, Decimal('99.96')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 828, Decimal('95.97')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 810, Decimal('79.96')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 924, Decimal('79.95')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 926, Decimal('79.95')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 93, Decimal('74.97')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 835, Decimal('63.98')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 897, Decimal('49.98')),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 1004, Decimal('10799.46')),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 365, Decimal('7978.67')),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 957, Decimal('6899.54')),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 191, Decimal('6799.32'))]" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT o.order_date,\n", " oi.order_item_product_id,\n", " round(sum(oi.order_item_subtotal)::numeric, 2) AS revenue\n", "FROM orders o JOIN order_items oi\n", "ON o.order_id = oi.order_item_order_id\n", "WHERE o.order_status IN ('COMPLETE', 'CLOSED')\n", "GROUP BY o.order_date, oi.order_item_product_id\n", "ORDER BY o.order_date, revenue DESC\n", "LIMIT 30" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Let us compute the rank for each product with in each date using revenue as criteria." ] }, { "cell_type": "code", "execution_count": 24, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "30 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
order_dateorder_item_product_idrevenuedrnk
2013-07-25 00:00:0010045599.721
2013-07-25 00:00:001915099.492
2013-07-25 00:00:009574499.703
2013-07-25 00:00:003653359.444
2013-07-25 00:00:0010732999.855
2013-07-25 00:00:0010142798.886
2013-07-25 00:00:004031949.857
2013-07-25 00:00:005021650.008
2013-07-25 00:00:006271079.739
2013-07-25 00:00:00226599.9910
2013-07-25 00:00:0024319.9611
2013-07-25 00:00:00821207.9612
2013-07-25 00:00:00625199.9913
2013-07-25 00:00:00705119.9914
2013-07-25 00:00:00572119.9715
2013-07-25 00:00:00666109.9916
2013-07-25 00:00:00725108.0017
2013-07-25 00:00:00134100.0018
2013-07-25 00:00:0090699.9619
2013-07-25 00:00:0082895.9720
2013-07-25 00:00:0081079.9621
2013-07-25 00:00:0092479.9522
2013-07-25 00:00:0092679.9522
2013-07-25 00:00:009374.9723
2013-07-25 00:00:0083563.9824
2013-07-25 00:00:0089749.9825
2013-07-26 00:00:00100410799.461
2013-07-26 00:00:003657978.672
2013-07-26 00:00:009576899.543
2013-07-26 00:00:001916799.324
" ], "text/plain": [ "[(datetime.datetime(2013, 7, 25, 0, 0), 1004, Decimal('5599.72'), 1),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 191, Decimal('5099.49'), 2),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 957, Decimal('4499.70'), 3),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 365, Decimal('3359.44'), 4),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 1073, Decimal('2999.85'), 5),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 1014, Decimal('2798.88'), 6),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 403, Decimal('1949.85'), 7),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 502, Decimal('1650.00'), 8),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 627, Decimal('1079.73'), 9),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 226, Decimal('599.99'), 10),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 24, Decimal('319.96'), 11),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 821, Decimal('207.96'), 12),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 625, Decimal('199.99'), 13),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 705, Decimal('119.99'), 14),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 572, Decimal('119.97'), 15),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 666, Decimal('109.99'), 16),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 725, Decimal('108.00'), 17),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 134, Decimal('100.00'), 18),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 906, Decimal('99.96'), 19),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 828, Decimal('95.97'), 20),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 810, Decimal('79.96'), 21),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 924, Decimal('79.95'), 22),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 926, Decimal('79.95'), 22),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 93, Decimal('74.97'), 23),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 835, Decimal('63.98'), 24),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 897, Decimal('49.98'), 25),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 1004, Decimal('10799.46'), 1),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 365, Decimal('7978.67'), 2),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 957, Decimal('6899.54'), 3),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 191, Decimal('6799.32'), 4)]" ] }, "execution_count": 24, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "\n", "SELECT nq.*,\n", " dense_rank() OVER (\n", " PARTITION BY order_date\n", " ORDER BY revenue DESC\n", " ) AS drnk\n", "FROM (\n", " SELECT o.order_date,\n", " oi.order_item_product_id,\n", " round(sum(oi.order_item_subtotal)::numeric, 2) AS revenue\n", " FROM orders o \n", " JOIN order_items oi\n", " ON o.order_id = oi.order_item_order_id\n", " WHERE o.order_status IN ('COMPLETE', 'CLOSED')\n", " GROUP BY o.order_date, oi.order_item_product_id\n", ") nq\n", "ORDER BY order_date, revenue DESC\n", "LIMIT 30" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Now let us see how we can filter the data." ] }, { "cell_type": "code", "execution_count": 25, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "20 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
order_dateorder_item_product_idrevenuedrnk
2013-07-25 00:00:0010045599.721
2013-07-25 00:00:001915099.492
2013-07-25 00:00:009574499.703
2013-07-25 00:00:003653359.444
2013-07-25 00:00:0010732999.855
2013-07-26 00:00:00100410799.461
2013-07-26 00:00:003657978.672
2013-07-26 00:00:009576899.543
2013-07-26 00:00:001916799.324
2013-07-26 00:00:0010144798.085
2013-07-27 00:00:0010049599.521
2013-07-27 00:00:001915999.402
2013-07-27 00:00:009575699.623
2013-07-27 00:00:0010735399.734
2013-07-27 00:00:003655099.155
2013-07-28 00:00:0010045599.721
2013-07-28 00:00:009575099.662
2013-07-28 00:00:003654799.203
2013-07-28 00:00:004034419.664
2013-07-28 00:00:001914299.575
" ], "text/plain": [ "[(datetime.datetime(2013, 7, 25, 0, 0), 1004, Decimal('5599.72'), 1),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 191, Decimal('5099.49'), 2),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 957, Decimal('4499.70'), 3),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 365, Decimal('3359.44'), 4),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 1073, Decimal('2999.85'), 5),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 1004, Decimal('10799.46'), 1),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 365, Decimal('7978.67'), 2),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 957, Decimal('6899.54'), 3),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 191, Decimal('6799.32'), 4),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 1014, Decimal('4798.08'), 5),\n", " (datetime.datetime(2013, 7, 27, 0, 0), 1004, Decimal('9599.52'), 1),\n", " (datetime.datetime(2013, 7, 27, 0, 0), 191, Decimal('5999.40'), 2),\n", " (datetime.datetime(2013, 7, 27, 0, 0), 957, Decimal('5699.62'), 3),\n", " (datetime.datetime(2013, 7, 27, 0, 0), 1073, Decimal('5399.73'), 4),\n", " (datetime.datetime(2013, 7, 27, 0, 0), 365, Decimal('5099.15'), 5),\n", " (datetime.datetime(2013, 7, 28, 0, 0), 1004, Decimal('5599.72'), 1),\n", " (datetime.datetime(2013, 7, 28, 0, 0), 957, Decimal('5099.66'), 2),\n", " (datetime.datetime(2013, 7, 28, 0, 0), 365, Decimal('4799.20'), 3),\n", " (datetime.datetime(2013, 7, 28, 0, 0), 403, Decimal('4419.66'), 4),\n", " (datetime.datetime(2013, 7, 28, 0, 0), 191, Decimal('4299.57'), 5)]" ] }, "execution_count": 25, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT * FROM (\n", " SELECT nq.*,\n", " dense_rank() OVER (\n", " PARTITION BY order_date\n", " ORDER BY revenue DESC\n", " ) AS drnk\n", " FROM (\n", " SELECT o.order_date,\n", " oi.order_item_product_id,\n", " round(sum(oi.order_item_subtotal)::numeric, 2) AS revenue\n", " FROM orders o \n", " JOIN order_items oi\n", " ON o.order_id = oi.order_item_order_id\n", " WHERE o.order_status IN ('COMPLETE', 'CLOSED')\n", " GROUP BY o.order_date, oi.order_item_product_id\n", " ) nq\n", ") nq1\n", "WHERE drnk <= 5\n", "ORDER BY order_date, revenue DESC\n", "LIMIT 20" ] }, { "cell_type": "code", "execution_count": 26, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "20 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
order_dateorder_item_product_idrevenuedrnk
2013-07-25 00:00:0010045599.721
2013-07-25 00:00:001915099.492
2013-07-25 00:00:009574499.703
2013-07-25 00:00:003653359.444
2013-07-25 00:00:0010732999.855
2013-07-26 00:00:00100410799.461
2013-07-26 00:00:003657978.672
2013-07-26 00:00:009576899.543
2013-07-26 00:00:001916799.324
2013-07-26 00:00:0010144798.085
2013-07-27 00:00:0010049599.521
2013-07-27 00:00:001915999.402
2013-07-27 00:00:009575699.623
2013-07-27 00:00:0010735399.734
2013-07-27 00:00:003655099.155
2013-07-28 00:00:0010045599.721
2013-07-28 00:00:009575099.662
2013-07-28 00:00:003654799.203
2013-07-28 00:00:004034419.664
2013-07-28 00:00:001914299.575
" ], "text/plain": [ "[(datetime.datetime(2013, 7, 25, 0, 0), 1004, Decimal('5599.72'), 1),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 191, Decimal('5099.49'), 2),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 957, Decimal('4499.70'), 3),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 365, Decimal('3359.44'), 4),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 1073, Decimal('2999.85'), 5),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 1004, Decimal('10799.46'), 1),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 365, Decimal('7978.67'), 2),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 957, Decimal('6899.54'), 3),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 191, Decimal('6799.32'), 4),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 1014, Decimal('4798.08'), 5),\n", " (datetime.datetime(2013, 7, 27, 0, 0), 1004, Decimal('9599.52'), 1),\n", " (datetime.datetime(2013, 7, 27, 0, 0), 191, Decimal('5999.40'), 2),\n", " (datetime.datetime(2013, 7, 27, 0, 0), 957, Decimal('5699.62'), 3),\n", " (datetime.datetime(2013, 7, 27, 0, 0), 1073, Decimal('5399.73'), 4),\n", " (datetime.datetime(2013, 7, 27, 0, 0), 365, Decimal('5099.15'), 5),\n", " (datetime.datetime(2013, 7, 28, 0, 0), 1004, Decimal('5599.72'), 1),\n", " (datetime.datetime(2013, 7, 28, 0, 0), 957, Decimal('5099.66'), 2),\n", " (datetime.datetime(2013, 7, 28, 0, 0), 365, Decimal('4799.20'), 3),\n", " (datetime.datetime(2013, 7, 28, 0, 0), 403, Decimal('4419.66'), 4),\n", " (datetime.datetime(2013, 7, 28, 0, 0), 191, Decimal('4299.57'), 5)]" ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT * FROM (SELECT dpr.*,\n", " dense_rank() OVER (\n", " PARTITION BY order_date\n", " ORDER BY revenue DESC\n", " ) AS drnk\n", "FROM daily_product_revenue AS dpr) q\n", "WHERE drnk <= 5\n", "ORDER BY order_date, revenue DESC\n", "LIMIT 20" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.6.12" } }, "nbformat": 4, "nbformat_minor": 4 }