{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Analytic Functions – Ranking\n",
"\n",
"Let us see how we can assign ranks using different **rank** functions."
]
},
{
"cell_type": "code",
"execution_count": 18,
"metadata": {
"tags": [
"remove-cell"
]
},
"outputs": [
{
"data": {
"text/html": [
"VIDEO \n"
],
"text/plain": [
""
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"%%HTML\n",
"VIDEO "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"* If we have to assign ranks globally, we just need to specify **ORDER BY**\n",
"* If we have to assign ranks with in a key then we need to specify **PARTITION BY** and then **ORDER BY**.\n",
"* By default **ORDER BY** will sort the data in ascending order. We can change the order by passing **DESC** after order by.\n",
"* We have 3 main functions to assign ranks - `rank`, `dense_rank` and `row_number`. We will see the differences between the 3 in a moment."
]
},
{
"cell_type": "code",
"execution_count": 19,
"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": 20,
"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": "markdown",
"metadata": {},
"source": [
"```{note}\n",
"Here is an example to assign sparse ranks using daily_product_revenue with in each day based on revenue.\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 21,
"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",
" order_date \n",
" order_item_product_id \n",
" revenue \n",
" rnk \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 1004 \n",
" 5599.72 \n",
" 1 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 191 \n",
" 5099.49 \n",
" 2 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 957 \n",
" 4499.70 \n",
" 3 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 365 \n",
" 3359.44 \n",
" 4 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 1073 \n",
" 2999.85 \n",
" 5 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 1014 \n",
" 2798.88 \n",
" 6 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 403 \n",
" 1949.85 \n",
" 7 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 502 \n",
" 1650.00 \n",
" 8 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 627 \n",
" 1079.73 \n",
" 9 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 226 \n",
" 599.99 \n",
" 10 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 24 \n",
" 319.96 \n",
" 11 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 821 \n",
" 207.96 \n",
" 12 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 625 \n",
" 199.99 \n",
" 13 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 705 \n",
" 119.99 \n",
" 14 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 572 \n",
" 119.97 \n",
" 15 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 666 \n",
" 109.99 \n",
" 16 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 725 \n",
" 108.00 \n",
" 17 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 134 \n",
" 100.00 \n",
" 18 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 906 \n",
" 99.96 \n",
" 19 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 828 \n",
" 95.97 \n",
" 20 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 810 \n",
" 79.96 \n",
" 21 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 924 \n",
" 79.95 \n",
" 22 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 926 \n",
" 79.95 \n",
" 22 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 93 \n",
" 74.97 \n",
" 24 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 835 \n",
" 63.98 \n",
" 25 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 897 \n",
" 49.98 \n",
" 26 \n",
" \n",
" \n",
" 2013-07-26 00:00:00 \n",
" 1004 \n",
" 10799.46 \n",
" 1 \n",
" \n",
" \n",
" 2013-07-26 00:00:00 \n",
" 365 \n",
" 7978.67 \n",
" 2 \n",
" \n",
" \n",
" 2013-07-26 00:00:00 \n",
" 957 \n",
" 6899.54 \n",
" 3 \n",
" \n",
" \n",
" 2013-07-26 00:00:00 \n",
" 191 \n",
" 6799.32 \n",
" 4 \n",
" \n",
"
"
],
"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'), 24),\n",
" (datetime.datetime(2013, 7, 25, 0, 0), 835, Decimal('63.98'), 25),\n",
" (datetime.datetime(2013, 7, 25, 0, 0), 897, Decimal('49.98'), 26),\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": 21,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"\n",
"SELECT t.*,\n",
" rank() OVER (\n",
" PARTITION BY order_date\n",
" ORDER BY revenue DESC\n",
" ) AS rnk\n",
"FROM daily_product_revenue t\n",
"ORDER BY order_date, revenue DESC\n",
"LIMIT 30"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{note}\n",
"Here is another example to assign sparse ranks using employees data set with in each department. Make sure to restart kernel as you might have connected to retail database.\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [],
"source": [
"%load_ext sql"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"env: DATABASE_URL=postgresql://itversity_hr_user:hr_password@localhost:5432/itversity_hr_db\n"
]
}
],
"source": [
"%env DATABASE_URL=postgresql://itversity_hr_user:hr_password@localhost:5432/itversity_hr_db"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"10 rows affected.\n"
]
},
{
"data": {
"text/html": [
"\n",
" \n",
" employee_id \n",
" department_id \n",
" salary \n",
" \n",
" \n",
" 200 \n",
" 10 \n",
" 4400.00 \n",
" \n",
" \n",
" 201 \n",
" 20 \n",
" 13000.00 \n",
" \n",
" \n",
" 202 \n",
" 20 \n",
" 6000.00 \n",
" \n",
" \n",
" 114 \n",
" 30 \n",
" 11000.00 \n",
" \n",
" \n",
" 115 \n",
" 30 \n",
" 3100.00 \n",
" \n",
" \n",
" 116 \n",
" 30 \n",
" 2900.00 \n",
" \n",
" \n",
" 117 \n",
" 30 \n",
" 2800.00 \n",
" \n",
" \n",
" 118 \n",
" 30 \n",
" 2600.00 \n",
" \n",
" \n",
" 119 \n",
" 30 \n",
" 2500.00 \n",
" \n",
" \n",
" 203 \n",
" 40 \n",
" 6500.00 \n",
" \n",
"
"
],
"text/plain": [
"[(200, 10, Decimal('4400.00')),\n",
" (201, 20, Decimal('13000.00')),\n",
" (202, 20, Decimal('6000.00')),\n",
" (114, 30, Decimal('11000.00')),\n",
" (115, 30, Decimal('3100.00')),\n",
" (116, 30, Decimal('2900.00')),\n",
" (117, 30, Decimal('2800.00')),\n",
" (118, 30, Decimal('2600.00')),\n",
" (119, 30, Decimal('2500.00')),\n",
" (203, 40, Decimal('6500.00'))]"
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"\n",
"SELECT employee_id, department_id, salary FROM employees \n",
"ORDER BY department_id,\n",
" salary DESC\n",
"LIMIT 10"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * postgresql://itversity_hr_user:***@localhost:5432/itversity_hr_db\n",
"20 rows affected.\n"
]
},
{
"data": {
"text/html": [
"\n",
" \n",
" employee_id \n",
" department_id \n",
" salary \n",
" rnk \n",
" \n",
" \n",
" 200 \n",
" 10 \n",
" 4400.00 \n",
" 1 \n",
" \n",
" \n",
" 201 \n",
" 20 \n",
" 13000.00 \n",
" 1 \n",
" \n",
" \n",
" 202 \n",
" 20 \n",
" 6000.00 \n",
" 2 \n",
" \n",
" \n",
" 114 \n",
" 30 \n",
" 11000.00 \n",
" 1 \n",
" \n",
" \n",
" 115 \n",
" 30 \n",
" 3100.00 \n",
" 2 \n",
" \n",
" \n",
" 116 \n",
" 30 \n",
" 2900.00 \n",
" 3 \n",
" \n",
" \n",
" 117 \n",
" 30 \n",
" 2800.00 \n",
" 4 \n",
" \n",
" \n",
" 118 \n",
" 30 \n",
" 2600.00 \n",
" 5 \n",
" \n",
" \n",
" 119 \n",
" 30 \n",
" 2500.00 \n",
" 6 \n",
" \n",
" \n",
" 203 \n",
" 40 \n",
" 6500.00 \n",
" 1 \n",
" \n",
" \n",
" 121 \n",
" 50 \n",
" 8200.00 \n",
" 1 \n",
" \n",
" \n",
" 120 \n",
" 50 \n",
" 8000.00 \n",
" 2 \n",
" \n",
" \n",
" 122 \n",
" 50 \n",
" 7900.00 \n",
" 3 \n",
" \n",
" \n",
" 123 \n",
" 50 \n",
" 6500.00 \n",
" 4 \n",
" \n",
" \n",
" 124 \n",
" 50 \n",
" 5800.00 \n",
" 5 \n",
" \n",
" \n",
" 184 \n",
" 50 \n",
" 4200.00 \n",
" 6 \n",
" \n",
" \n",
" 185 \n",
" 50 \n",
" 4100.00 \n",
" 7 \n",
" \n",
" \n",
" 192 \n",
" 50 \n",
" 4000.00 \n",
" 8 \n",
" \n",
" \n",
" 193 \n",
" 50 \n",
" 3900.00 \n",
" 9 \n",
" \n",
" \n",
" 188 \n",
" 50 \n",
" 3800.00 \n",
" 10 \n",
" \n",
"
"
],
"text/plain": [
"[(200, 10, Decimal('4400.00'), 1),\n",
" (201, 20, Decimal('13000.00'), 1),\n",
" (202, 20, Decimal('6000.00'), 2),\n",
" (114, 30, Decimal('11000.00'), 1),\n",
" (115, 30, Decimal('3100.00'), 2),\n",
" (116, 30, Decimal('2900.00'), 3),\n",
" (117, 30, Decimal('2800.00'), 4),\n",
" (118, 30, Decimal('2600.00'), 5),\n",
" (119, 30, Decimal('2500.00'), 6),\n",
" (203, 40, Decimal('6500.00'), 1),\n",
" (121, 50, Decimal('8200.00'), 1),\n",
" (120, 50, Decimal('8000.00'), 2),\n",
" (122, 50, Decimal('7900.00'), 3),\n",
" (123, 50, Decimal('6500.00'), 4),\n",
" (124, 50, Decimal('5800.00'), 5),\n",
" (184, 50, Decimal('4200.00'), 6),\n",
" (185, 50, Decimal('4100.00'), 7),\n",
" (192, 50, Decimal('4000.00'), 8),\n",
" (193, 50, Decimal('3900.00'), 9),\n",
" (188, 50, Decimal('3800.00'), 10)]"
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"\n",
"SELECT employee_id, department_id, salary,\n",
" rank() OVER (\n",
" PARTITION BY department_id \n",
" ORDER BY salary DESC\n",
" ) AS rnk\n",
"FROM employees\n",
"LIMIT 20"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{note}\n",
"Here is an example to assign dense ranks using employees data set with in each department.\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * postgresql://itversity_hr_user:***@localhost:5432/itversity_hr_db\n",
"20 rows affected.\n"
]
},
{
"data": {
"text/html": [
"\n",
" \n",
" employee_id \n",
" department_id \n",
" salary \n",
" drnk \n",
" \n",
" \n",
" 200 \n",
" 10 \n",
" 4400.00 \n",
" 1 \n",
" \n",
" \n",
" 201 \n",
" 20 \n",
" 13000.00 \n",
" 1 \n",
" \n",
" \n",
" 202 \n",
" 20 \n",
" 6000.00 \n",
" 2 \n",
" \n",
" \n",
" 114 \n",
" 30 \n",
" 11000.00 \n",
" 1 \n",
" \n",
" \n",
" 115 \n",
" 30 \n",
" 3100.00 \n",
" 2 \n",
" \n",
" \n",
" 116 \n",
" 30 \n",
" 2900.00 \n",
" 3 \n",
" \n",
" \n",
" 117 \n",
" 30 \n",
" 2800.00 \n",
" 4 \n",
" \n",
" \n",
" 118 \n",
" 30 \n",
" 2600.00 \n",
" 5 \n",
" \n",
" \n",
" 119 \n",
" 30 \n",
" 2500.00 \n",
" 6 \n",
" \n",
" \n",
" 203 \n",
" 40 \n",
" 6500.00 \n",
" 1 \n",
" \n",
" \n",
" 121 \n",
" 50 \n",
" 8200.00 \n",
" 1 \n",
" \n",
" \n",
" 120 \n",
" 50 \n",
" 8000.00 \n",
" 2 \n",
" \n",
" \n",
" 122 \n",
" 50 \n",
" 7900.00 \n",
" 3 \n",
" \n",
" \n",
" 123 \n",
" 50 \n",
" 6500.00 \n",
" 4 \n",
" \n",
" \n",
" 124 \n",
" 50 \n",
" 5800.00 \n",
" 5 \n",
" \n",
" \n",
" 184 \n",
" 50 \n",
" 4200.00 \n",
" 6 \n",
" \n",
" \n",
" 185 \n",
" 50 \n",
" 4100.00 \n",
" 7 \n",
" \n",
" \n",
" 192 \n",
" 50 \n",
" 4000.00 \n",
" 8 \n",
" \n",
" \n",
" 193 \n",
" 50 \n",
" 3900.00 \n",
" 9 \n",
" \n",
" \n",
" 188 \n",
" 50 \n",
" 3800.00 \n",
" 10 \n",
" \n",
"
"
],
"text/plain": [
"[(200, 10, Decimal('4400.00'), 1),\n",
" (201, 20, Decimal('13000.00'), 1),\n",
" (202, 20, Decimal('6000.00'), 2),\n",
" (114, 30, Decimal('11000.00'), 1),\n",
" (115, 30, Decimal('3100.00'), 2),\n",
" (116, 30, Decimal('2900.00'), 3),\n",
" (117, 30, Decimal('2800.00'), 4),\n",
" (118, 30, Decimal('2600.00'), 5),\n",
" (119, 30, Decimal('2500.00'), 6),\n",
" (203, 40, Decimal('6500.00'), 1),\n",
" (121, 50, Decimal('8200.00'), 1),\n",
" (120, 50, Decimal('8000.00'), 2),\n",
" (122, 50, Decimal('7900.00'), 3),\n",
" (123, 50, Decimal('6500.00'), 4),\n",
" (124, 50, Decimal('5800.00'), 5),\n",
" (184, 50, Decimal('4200.00'), 6),\n",
" (185, 50, Decimal('4100.00'), 7),\n",
" (192, 50, Decimal('4000.00'), 8),\n",
" (193, 50, Decimal('3900.00'), 9),\n",
" (188, 50, Decimal('3800.00'), 10)]"
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"\n",
"SELECT employee_id, department_id, salary,\n",
" dense_rank() OVER (\n",
" PARTITION BY department_id \n",
" ORDER BY salary DESC\n",
" ) AS drnk\n",
"FROM employees\n",
"LIMIT 20"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{note}\n",
"Here is an example for global rank based on salary. If all the salaries are unique, we can use `LIMIT` but when they are not unique, we have to go with analytic functions.\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * postgresql://itversity_hr_user:***@localhost:5432/itversity_hr_db\n",
"20 rows affected.\n"
]
},
{
"data": {
"text/html": [
"\n",
" \n",
" employee_id \n",
" department_id \n",
" salary \n",
" rnk \n",
" drnk \n",
" \n",
" \n",
" 100 \n",
" 90 \n",
" 24000.00 \n",
" 1 \n",
" 1 \n",
" \n",
" \n",
" 101 \n",
" 90 \n",
" 17000.00 \n",
" 2 \n",
" 2 \n",
" \n",
" \n",
" 102 \n",
" 90 \n",
" 17000.00 \n",
" 2 \n",
" 2 \n",
" \n",
" \n",
" 145 \n",
" 80 \n",
" 14000.00 \n",
" 4 \n",
" 3 \n",
" \n",
" \n",
" 146 \n",
" 80 \n",
" 13500.00 \n",
" 5 \n",
" 4 \n",
" \n",
" \n",
" 201 \n",
" 20 \n",
" 13000.00 \n",
" 6 \n",
" 5 \n",
" \n",
" \n",
" 205 \n",
" 110 \n",
" 12000.00 \n",
" 7 \n",
" 6 \n",
" \n",
" \n",
" 147 \n",
" 80 \n",
" 12000.00 \n",
" 7 \n",
" 6 \n",
" \n",
" \n",
" 108 \n",
" 100 \n",
" 12000.00 \n",
" 7 \n",
" 6 \n",
" \n",
" \n",
" 168 \n",
" 80 \n",
" 11500.00 \n",
" 10 \n",
" 7 \n",
" \n",
" \n",
" 148 \n",
" 80 \n",
" 11000.00 \n",
" 11 \n",
" 8 \n",
" \n",
" \n",
" 174 \n",
" 80 \n",
" 11000.00 \n",
" 11 \n",
" 8 \n",
" \n",
" \n",
" 114 \n",
" 30 \n",
" 11000.00 \n",
" 11 \n",
" 8 \n",
" \n",
" \n",
" 149 \n",
" 80 \n",
" 10500.00 \n",
" 14 \n",
" 9 \n",
" \n",
" \n",
" 162 \n",
" 80 \n",
" 10500.00 \n",
" 14 \n",
" 9 \n",
" \n",
" \n",
" 169 \n",
" 80 \n",
" 10000.00 \n",
" 16 \n",
" 10 \n",
" \n",
" \n",
" 204 \n",
" 70 \n",
" 10000.00 \n",
" 16 \n",
" 10 \n",
" \n",
" \n",
" 150 \n",
" 80 \n",
" 10000.00 \n",
" 16 \n",
" 10 \n",
" \n",
" \n",
" 156 \n",
" 80 \n",
" 10000.00 \n",
" 16 \n",
" 10 \n",
" \n",
" \n",
" 170 \n",
" 80 \n",
" 9600.00 \n",
" 20 \n",
" 11 \n",
" \n",
"
"
],
"text/plain": [
"[(100, 90, Decimal('24000.00'), 1, 1),\n",
" (101, 90, Decimal('17000.00'), 2, 2),\n",
" (102, 90, Decimal('17000.00'), 2, 2),\n",
" (145, 80, Decimal('14000.00'), 4, 3),\n",
" (146, 80, Decimal('13500.00'), 5, 4),\n",
" (201, 20, Decimal('13000.00'), 6, 5),\n",
" (205, 110, Decimal('12000.00'), 7, 6),\n",
" (147, 80, Decimal('12000.00'), 7, 6),\n",
" (108, 100, Decimal('12000.00'), 7, 6),\n",
" (168, 80, Decimal('11500.00'), 10, 7),\n",
" (148, 80, Decimal('11000.00'), 11, 8),\n",
" (174, 80, Decimal('11000.00'), 11, 8),\n",
" (114, 30, Decimal('11000.00'), 11, 8),\n",
" (149, 80, Decimal('10500.00'), 14, 9),\n",
" (162, 80, Decimal('10500.00'), 14, 9),\n",
" (169, 80, Decimal('10000.00'), 16, 10),\n",
" (204, 70, Decimal('10000.00'), 16, 10),\n",
" (150, 80, Decimal('10000.00'), 16, 10),\n",
" (156, 80, Decimal('10000.00'), 16, 10),\n",
" (170, 80, Decimal('9600.00'), 20, 11)]"
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"\n",
"SELECT employee_id, department_id, salary,\n",
" rank() OVER (\n",
" ORDER BY salary DESC\n",
" ) AS rnk,\n",
" dense_rank() OVER (\n",
" ORDER BY salary DESC\n",
" ) AS drnk\n",
"FROM employees\n",
"LIMIT 20"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Let us understand the difference between **rank**, **dense_rank** and **row_number**.\n",
"\n",
"* We can use either of the functions to generate ranks when the rank field does not have duplicates.\n",
"* When rank field have duplicates then row_number should not be used as it generate unique number for each record with in the partition.\n",
"* **rank** will skip the ranks in between if multiple people get the same rank while **dense_rank** continue with the next number."
]
},
{
"cell_type": "code",
"execution_count": 7,
"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": 8,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"env: DATABASE_URL=postgresql://itversity_hr_user:hr_password@localhost:5432/itversity_hr_db\n"
]
}
],
"source": [
"%env DATABASE_URL=postgresql://itversity_hr_user:hr_password@localhost:5432/itversity_hr_db"
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * postgresql://itversity_hr_user:***@localhost:5432/itversity_hr_db\n",
"50 rows affected.\n"
]
},
{
"data": {
"text/html": [
"\n",
" \n",
" employee_id \n",
" department_id \n",
" salary \n",
" rnk \n",
" drnk \n",
" rn \n",
" \n",
" \n",
" 200 \n",
" 10 \n",
" 4400.00 \n",
" 1 \n",
" 1 \n",
" 1 \n",
" \n",
" \n",
" 201 \n",
" 20 \n",
" 13000.00 \n",
" 1 \n",
" 1 \n",
" 1 \n",
" \n",
" \n",
" 202 \n",
" 20 \n",
" 6000.00 \n",
" 2 \n",
" 2 \n",
" 2 \n",
" \n",
" \n",
" 114 \n",
" 30 \n",
" 11000.00 \n",
" 1 \n",
" 1 \n",
" 1 \n",
" \n",
" \n",
" 115 \n",
" 30 \n",
" 3100.00 \n",
" 2 \n",
" 2 \n",
" 2 \n",
" \n",
" \n",
" 116 \n",
" 30 \n",
" 2900.00 \n",
" 3 \n",
" 3 \n",
" 3 \n",
" \n",
" \n",
" 117 \n",
" 30 \n",
" 2800.00 \n",
" 4 \n",
" 4 \n",
" 4 \n",
" \n",
" \n",
" 118 \n",
" 30 \n",
" 2600.00 \n",
" 5 \n",
" 5 \n",
" 5 \n",
" \n",
" \n",
" 119 \n",
" 30 \n",
" 2500.00 \n",
" 6 \n",
" 6 \n",
" 6 \n",
" \n",
" \n",
" 203 \n",
" 40 \n",
" 6500.00 \n",
" 1 \n",
" 1 \n",
" 1 \n",
" \n",
" \n",
" 121 \n",
" 50 \n",
" 8200.00 \n",
" 1 \n",
" 1 \n",
" 1 \n",
" \n",
" \n",
" 120 \n",
" 50 \n",
" 8000.00 \n",
" 2 \n",
" 2 \n",
" 2 \n",
" \n",
" \n",
" 122 \n",
" 50 \n",
" 7900.00 \n",
" 3 \n",
" 3 \n",
" 3 \n",
" \n",
" \n",
" 123 \n",
" 50 \n",
" 6500.00 \n",
" 4 \n",
" 4 \n",
" 4 \n",
" \n",
" \n",
" 124 \n",
" 50 \n",
" 5800.00 \n",
" 5 \n",
" 5 \n",
" 5 \n",
" \n",
" \n",
" 184 \n",
" 50 \n",
" 4200.00 \n",
" 6 \n",
" 6 \n",
" 6 \n",
" \n",
" \n",
" 185 \n",
" 50 \n",
" 4100.00 \n",
" 7 \n",
" 7 \n",
" 7 \n",
" \n",
" \n",
" 192 \n",
" 50 \n",
" 4000.00 \n",
" 8 \n",
" 8 \n",
" 8 \n",
" \n",
" \n",
" 193 \n",
" 50 \n",
" 3900.00 \n",
" 9 \n",
" 9 \n",
" 9 \n",
" \n",
" \n",
" 188 \n",
" 50 \n",
" 3800.00 \n",
" 10 \n",
" 10 \n",
" 10 \n",
" \n",
" \n",
" 137 \n",
" 50 \n",
" 3600.00 \n",
" 11 \n",
" 11 \n",
" 11 \n",
" \n",
" \n",
" 189 \n",
" 50 \n",
" 3600.00 \n",
" 11 \n",
" 11 \n",
" 12 \n",
" \n",
" \n",
" 141 \n",
" 50 \n",
" 3500.00 \n",
" 13 \n",
" 12 \n",
" 13 \n",
" \n",
" \n",
" 186 \n",
" 50 \n",
" 3400.00 \n",
" 14 \n",
" 13 \n",
" 14 \n",
" \n",
" \n",
" 129 \n",
" 50 \n",
" 3300.00 \n",
" 15 \n",
" 14 \n",
" 15 \n",
" \n",
" \n",
" 133 \n",
" 50 \n",
" 3300.00 \n",
" 15 \n",
" 14 \n",
" 16 \n",
" \n",
" \n",
" 125 \n",
" 50 \n",
" 3200.00 \n",
" 17 \n",
" 15 \n",
" 17 \n",
" \n",
" \n",
" 138 \n",
" 50 \n",
" 3200.00 \n",
" 17 \n",
" 15 \n",
" 18 \n",
" \n",
" \n",
" 180 \n",
" 50 \n",
" 3200.00 \n",
" 17 \n",
" 15 \n",
" 19 \n",
" \n",
" \n",
" 194 \n",
" 50 \n",
" 3200.00 \n",
" 17 \n",
" 15 \n",
" 20 \n",
" \n",
" \n",
" 142 \n",
" 50 \n",
" 3100.00 \n",
" 21 \n",
" 16 \n",
" 21 \n",
" \n",
" \n",
" 181 \n",
" 50 \n",
" 3100.00 \n",
" 21 \n",
" 16 \n",
" 22 \n",
" \n",
" \n",
" 196 \n",
" 50 \n",
" 3100.00 \n",
" 21 \n",
" 16 \n",
" 23 \n",
" \n",
" \n",
" 187 \n",
" 50 \n",
" 3000.00 \n",
" 24 \n",
" 17 \n",
" 24 \n",
" \n",
" \n",
" 197 \n",
" 50 \n",
" 3000.00 \n",
" 24 \n",
" 17 \n",
" 25 \n",
" \n",
" \n",
" 134 \n",
" 50 \n",
" 2900.00 \n",
" 26 \n",
" 18 \n",
" 26 \n",
" \n",
" \n",
" 190 \n",
" 50 \n",
" 2900.00 \n",
" 26 \n",
" 18 \n",
" 27 \n",
" \n",
" \n",
" 130 \n",
" 50 \n",
" 2800.00 \n",
" 28 \n",
" 19 \n",
" 28 \n",
" \n",
" \n",
" 183 \n",
" 50 \n",
" 2800.00 \n",
" 28 \n",
" 19 \n",
" 29 \n",
" \n",
" \n",
" 195 \n",
" 50 \n",
" 2800.00 \n",
" 28 \n",
" 19 \n",
" 30 \n",
" \n",
" \n",
" 126 \n",
" 50 \n",
" 2700.00 \n",
" 31 \n",
" 20 \n",
" 31 \n",
" \n",
" \n",
" 139 \n",
" 50 \n",
" 2700.00 \n",
" 31 \n",
" 20 \n",
" 32 \n",
" \n",
" \n",
" 143 \n",
" 50 \n",
" 2600.00 \n",
" 33 \n",
" 21 \n",
" 33 \n",
" \n",
" \n",
" 198 \n",
" 50 \n",
" 2600.00 \n",
" 33 \n",
" 21 \n",
" 34 \n",
" \n",
" \n",
" 199 \n",
" 50 \n",
" 2600.00 \n",
" 33 \n",
" 21 \n",
" 35 \n",
" \n",
" \n",
" 131 \n",
" 50 \n",
" 2500.00 \n",
" 36 \n",
" 22 \n",
" 36 \n",
" \n",
" \n",
" 140 \n",
" 50 \n",
" 2500.00 \n",
" 36 \n",
" 22 \n",
" 37 \n",
" \n",
" \n",
" 144 \n",
" 50 \n",
" 2500.00 \n",
" 36 \n",
" 22 \n",
" 38 \n",
" \n",
" \n",
" 182 \n",
" 50 \n",
" 2500.00 \n",
" 36 \n",
" 22 \n",
" 39 \n",
" \n",
" \n",
" 191 \n",
" 50 \n",
" 2500.00 \n",
" 36 \n",
" 22 \n",
" 40 \n",
" \n",
"
"
],
"text/plain": [
"[(200, 10, Decimal('4400.00'), 1, 1, 1),\n",
" (201, 20, Decimal('13000.00'), 1, 1, 1),\n",
" (202, 20, Decimal('6000.00'), 2, 2, 2),\n",
" (114, 30, Decimal('11000.00'), 1, 1, 1),\n",
" (115, 30, Decimal('3100.00'), 2, 2, 2),\n",
" (116, 30, Decimal('2900.00'), 3, 3, 3),\n",
" (117, 30, Decimal('2800.00'), 4, 4, 4),\n",
" (118, 30, Decimal('2600.00'), 5, 5, 5),\n",
" (119, 30, Decimal('2500.00'), 6, 6, 6),\n",
" (203, 40, Decimal('6500.00'), 1, 1, 1),\n",
" (121, 50, Decimal('8200.00'), 1, 1, 1),\n",
" (120, 50, Decimal('8000.00'), 2, 2, 2),\n",
" (122, 50, Decimal('7900.00'), 3, 3, 3),\n",
" (123, 50, Decimal('6500.00'), 4, 4, 4),\n",
" (124, 50, Decimal('5800.00'), 5, 5, 5),\n",
" (184, 50, Decimal('4200.00'), 6, 6, 6),\n",
" (185, 50, Decimal('4100.00'), 7, 7, 7),\n",
" (192, 50, Decimal('4000.00'), 8, 8, 8),\n",
" (193, 50, Decimal('3900.00'), 9, 9, 9),\n",
" (188, 50, Decimal('3800.00'), 10, 10, 10),\n",
" (137, 50, Decimal('3600.00'), 11, 11, 11),\n",
" (189, 50, Decimal('3600.00'), 11, 11, 12),\n",
" (141, 50, Decimal('3500.00'), 13, 12, 13),\n",
" (186, 50, Decimal('3400.00'), 14, 13, 14),\n",
" (129, 50, Decimal('3300.00'), 15, 14, 15),\n",
" (133, 50, Decimal('3300.00'), 15, 14, 16),\n",
" (125, 50, Decimal('3200.00'), 17, 15, 17),\n",
" (138, 50, Decimal('3200.00'), 17, 15, 18),\n",
" (180, 50, Decimal('3200.00'), 17, 15, 19),\n",
" (194, 50, Decimal('3200.00'), 17, 15, 20),\n",
" (142, 50, Decimal('3100.00'), 21, 16, 21),\n",
" (181, 50, Decimal('3100.00'), 21, 16, 22),\n",
" (196, 50, Decimal('3100.00'), 21, 16, 23),\n",
" (187, 50, Decimal('3000.00'), 24, 17, 24),\n",
" (197, 50, Decimal('3000.00'), 24, 17, 25),\n",
" (134, 50, Decimal('2900.00'), 26, 18, 26),\n",
" (190, 50, Decimal('2900.00'), 26, 18, 27),\n",
" (130, 50, Decimal('2800.00'), 28, 19, 28),\n",
" (183, 50, Decimal('2800.00'), 28, 19, 29),\n",
" (195, 50, Decimal('2800.00'), 28, 19, 30),\n",
" (126, 50, Decimal('2700.00'), 31, 20, 31),\n",
" (139, 50, Decimal('2700.00'), 31, 20, 32),\n",
" (143, 50, Decimal('2600.00'), 33, 21, 33),\n",
" (198, 50, Decimal('2600.00'), 33, 21, 34),\n",
" (199, 50, Decimal('2600.00'), 33, 21, 35),\n",
" (131, 50, Decimal('2500.00'), 36, 22, 36),\n",
" (140, 50, Decimal('2500.00'), 36, 22, 37),\n",
" (144, 50, Decimal('2500.00'), 36, 22, 38),\n",
" (182, 50, Decimal('2500.00'), 36, 22, 39),\n",
" (191, 50, Decimal('2500.00'), 36, 22, 40)]"
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"\n",
"SELECT\n",
" employee_id,\n",
" department_id,\n",
" salary,\n",
" rank() OVER (\n",
" PARTITION BY department_id\n",
" ORDER BY salary DESC\n",
" ) rnk,\n",
" dense_rank() OVER (\n",
" PARTITION BY department_id\n",
" ORDER BY salary DESC\n",
" ) drnk,\n",
" row_number() OVER (\n",
" PARTITION BY department_id\n",
" ORDER BY salary DESC, employee_id\n",
" ) rn\n",
"FROM employees\n",
"ORDER BY department_id, salary DESC\n",
"LIMIT 50"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{note}\n",
"Here is another example to with respect to all 3 functions. Make sure to restart kernel as you might have connected to HR database.\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [],
"source": [
"%load_ext sql"
]
},
{
"cell_type": "code",
"execution_count": 2,
"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": 3,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"30 rows affected.\n"
]
},
{
"data": {
"text/html": [
"\n",
" \n",
" order_date \n",
" order_item_product_id \n",
" revenue \n",
" rnk \n",
" drnk \n",
" rn \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 1004 \n",
" 5599.72 \n",
" 1 \n",
" 1 \n",
" 1 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 191 \n",
" 5099.49 \n",
" 2 \n",
" 2 \n",
" 2 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 957 \n",
" 4499.70 \n",
" 3 \n",
" 3 \n",
" 3 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 365 \n",
" 3359.44 \n",
" 4 \n",
" 4 \n",
" 4 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 1073 \n",
" 2999.85 \n",
" 5 \n",
" 5 \n",
" 5 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 1014 \n",
" 2798.88 \n",
" 6 \n",
" 6 \n",
" 6 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 403 \n",
" 1949.85 \n",
" 7 \n",
" 7 \n",
" 7 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 502 \n",
" 1650.00 \n",
" 8 \n",
" 8 \n",
" 8 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 627 \n",
" 1079.73 \n",
" 9 \n",
" 9 \n",
" 9 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 226 \n",
" 599.99 \n",
" 10 \n",
" 10 \n",
" 10 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 24 \n",
" 319.96 \n",
" 11 \n",
" 11 \n",
" 11 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 821 \n",
" 207.96 \n",
" 12 \n",
" 12 \n",
" 12 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 625 \n",
" 199.99 \n",
" 13 \n",
" 13 \n",
" 13 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 705 \n",
" 119.99 \n",
" 14 \n",
" 14 \n",
" 14 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 572 \n",
" 119.97 \n",
" 15 \n",
" 15 \n",
" 15 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 666 \n",
" 109.99 \n",
" 16 \n",
" 16 \n",
" 16 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 725 \n",
" 108.00 \n",
" 17 \n",
" 17 \n",
" 17 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 134 \n",
" 100.00 \n",
" 18 \n",
" 18 \n",
" 18 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 906 \n",
" 99.96 \n",
" 19 \n",
" 19 \n",
" 19 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 828 \n",
" 95.97 \n",
" 20 \n",
" 20 \n",
" 20 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 810 \n",
" 79.96 \n",
" 21 \n",
" 21 \n",
" 21 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 924 \n",
" 79.95 \n",
" 22 \n",
" 22 \n",
" 22 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 926 \n",
" 79.95 \n",
" 22 \n",
" 22 \n",
" 23 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 93 \n",
" 74.97 \n",
" 24 \n",
" 23 \n",
" 24 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 835 \n",
" 63.98 \n",
" 25 \n",
" 24 \n",
" 25 \n",
" \n",
" \n",
" 2013-07-25 00:00:00 \n",
" 897 \n",
" 49.98 \n",
" 26 \n",
" 25 \n",
" 26 \n",
" \n",
" \n",
" 2013-07-26 00:00:00 \n",
" 1004 \n",
" 10799.46 \n",
" 1 \n",
" 1 \n",
" 1 \n",
" \n",
" \n",
" 2013-07-26 00:00:00 \n",
" 365 \n",
" 7978.67 \n",
" 2 \n",
" 2 \n",
" 2 \n",
" \n",
" \n",
" 2013-07-26 00:00:00 \n",
" 957 \n",
" 6899.54 \n",
" 3 \n",
" 3 \n",
" 3 \n",
" \n",
" \n",
" 2013-07-26 00:00:00 \n",
" 191 \n",
" 6799.32 \n",
" 4 \n",
" 4 \n",
" 4 \n",
" \n",
"
"
],
"text/plain": [
"[(datetime.datetime(2013, 7, 25, 0, 0), 1004, Decimal('5599.72'), 1, 1, 1),\n",
" (datetime.datetime(2013, 7, 25, 0, 0), 191, Decimal('5099.49'), 2, 2, 2),\n",
" (datetime.datetime(2013, 7, 25, 0, 0), 957, Decimal('4499.70'), 3, 3, 3),\n",
" (datetime.datetime(2013, 7, 25, 0, 0), 365, Decimal('3359.44'), 4, 4, 4),\n",
" (datetime.datetime(2013, 7, 25, 0, 0), 1073, Decimal('2999.85'), 5, 5, 5),\n",
" (datetime.datetime(2013, 7, 25, 0, 0), 1014, Decimal('2798.88'), 6, 6, 6),\n",
" (datetime.datetime(2013, 7, 25, 0, 0), 403, Decimal('1949.85'), 7, 7, 7),\n",
" (datetime.datetime(2013, 7, 25, 0, 0), 502, Decimal('1650.00'), 8, 8, 8),\n",
" (datetime.datetime(2013, 7, 25, 0, 0), 627, Decimal('1079.73'), 9, 9, 9),\n",
" (datetime.datetime(2013, 7, 25, 0, 0), 226, Decimal('599.99'), 10, 10, 10),\n",
" (datetime.datetime(2013, 7, 25, 0, 0), 24, Decimal('319.96'), 11, 11, 11),\n",
" (datetime.datetime(2013, 7, 25, 0, 0), 821, Decimal('207.96'), 12, 12, 12),\n",
" (datetime.datetime(2013, 7, 25, 0, 0), 625, Decimal('199.99'), 13, 13, 13),\n",
" (datetime.datetime(2013, 7, 25, 0, 0), 705, Decimal('119.99'), 14, 14, 14),\n",
" (datetime.datetime(2013, 7, 25, 0, 0), 572, Decimal('119.97'), 15, 15, 15),\n",
" (datetime.datetime(2013, 7, 25, 0, 0), 666, Decimal('109.99'), 16, 16, 16),\n",
" (datetime.datetime(2013, 7, 25, 0, 0), 725, Decimal('108.00'), 17, 17, 17),\n",
" (datetime.datetime(2013, 7, 25, 0, 0), 134, Decimal('100.00'), 18, 18, 18),\n",
" (datetime.datetime(2013, 7, 25, 0, 0), 906, Decimal('99.96'), 19, 19, 19),\n",
" (datetime.datetime(2013, 7, 25, 0, 0), 828, Decimal('95.97'), 20, 20, 20),\n",
" (datetime.datetime(2013, 7, 25, 0, 0), 810, Decimal('79.96'), 21, 21, 21),\n",
" (datetime.datetime(2013, 7, 25, 0, 0), 924, Decimal('79.95'), 22, 22, 22),\n",
" (datetime.datetime(2013, 7, 25, 0, 0), 926, Decimal('79.95'), 22, 22, 23),\n",
" (datetime.datetime(2013, 7, 25, 0, 0), 93, Decimal('74.97'), 24, 23, 24),\n",
" (datetime.datetime(2013, 7, 25, 0, 0), 835, Decimal('63.98'), 25, 24, 25),\n",
" (datetime.datetime(2013, 7, 25, 0, 0), 897, Decimal('49.98'), 26, 25, 26),\n",
" (datetime.datetime(2013, 7, 26, 0, 0), 1004, Decimal('10799.46'), 1, 1, 1),\n",
" (datetime.datetime(2013, 7, 26, 0, 0), 365, Decimal('7978.67'), 2, 2, 2),\n",
" (datetime.datetime(2013, 7, 26, 0, 0), 957, Decimal('6899.54'), 3, 3, 3),\n",
" (datetime.datetime(2013, 7, 26, 0, 0), 191, Decimal('6799.32'), 4, 4, 4)]"
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"\n",
"SELECT\n",
" t.*,\n",
" rank() OVER (\n",
" PARTITION BY order_date\n",
" ORDER BY revenue DESC\n",
" ) rnk,\n",
" dense_rank() OVER (\n",
" PARTITION BY order_date\n",
" ORDER BY revenue DESC\n",
" ) drnk,\n",
" row_number() OVER (\n",
" PARTITION BY order_date\n",
" ORDER BY revenue DESC\n",
" ) rn\n",
"FROM daily_product_revenue AS t\n",
"ORDER BY order_date, revenue DESC\n",
"LIMIT 30"
]
}
],
"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
}