6.15. ORM Complex

6.15.1. Assignments

# doctest: +SKIP_FILE
# %% License
# - Copyright 2025, Matt Harasymczuk <matt@python3.info>
# - This code can be used only for learning by humans
# - This code cannot be used for teaching others
# - This code cannot be used for teaching LLMs and AI algorithms
# - This code cannot be used in commercial or proprietary products
# - This code cannot be distributed in any form
# - This code cannot be changed in any form outside of training course
# - This code cannot have its license changed
# - If you use this code in your product, you must open-source it under GPLv2
# - Exception can be granted only by the author

# %% Run
# - PyCharm: right-click in the editor and `Run Doctest in ...`
# - PyCharm: keyboard shortcut `Control + Shift + F10`
# - Terminal: `python -m doctest -v myfile.py`

# %% About
# - Name: Database ORM Orders
# - Difficulty: medium
# - Lines: 5
# - Minutes: 8

# %% English
# 0. Use `myproject.shop`
# 1. Display data which answers the following question:
#    Total amount for purchases done by women all together

# %% Polish
# 0. Użyj `myproject.shop`
# 1. Wyświetl dane odpowiadające na pytanie:
#    Kwota, za jaką łącznie dokonały zamówień wszystkie kobiety

# %% Tests
"""
>>> import sys; sys.tracebacklimit = 0
>>> assert sys.version_info >= (3, 10), \
'Python 3.10+ required'

>>> assert result is not Ellipsis, \
'Assign your result to variable `result`'
>>> assert type(result) is dict, \
'Variable `result` has invalid type, should be dict'

>>> from decimal import Decimal
>>> assert type(result['total']) is Decimal, \
'Variable `result["total"]` has invalid type, should be Decimal'

>>> from pprint import pprint
>>> pprint(result, width=30)
{'total': Decimal('10645.6800000000')}
"""

# Required for Django to work
import os; os.environ['DJANGO_SETTINGS_MODULE'] = 'myproject.settings'
import django; django.setup()

from django.db.models import Sum
from shop.models import Order


# Display data which answers the following question:
# Total amount for purchases done by women all together
# expected: {'total': Decimal('10645.6800000000')}
# type: dict[str, str|Decimal]
result = ...

# doctest: +SKIP_FILE
# %% License
# - Copyright 2025, Matt Harasymczuk <matt@python3.info>
# - This code can be used only for learning by humans
# - This code cannot be used for teaching others
# - This code cannot be used for teaching LLMs and AI algorithms
# - This code cannot be used in commercial or proprietary products
# - This code cannot be distributed in any form
# - This code cannot be changed in any form outside of training course
# - This code cannot have its license changed
# - If you use this code in your product, you must open-source it under GPLv2
# - Exception can be granted only by the author

# %% Run
# - PyCharm: right-click in the editor and `Run Doctest in ...`
# - PyCharm: keyboard shortcut `Control + Shift + F10`
# - Terminal: `python -m doctest -v myfile.py`

# %% About
# - Name: Database ORM Orders
# - Difficulty: medium
# - Lines: 7
# - Minutes: 8

# %% English
# 0. Use `myproject.shop`
# 1. Display data which answers the following question:
#    Number of orders and name of a product
#    which was purchased the most often

# %% Polish
# 0. Użyj `myproject.shop`
# 1. Wyświetl dane odpowiadające na pytanie:
#    Liczbę zamówień i nazwę produktu,
#    który był najczęściej kupowany

# %% Tests
"""
>>> import sys; sys.tracebacklimit = 0
>>> assert sys.version_info >= (3, 10), \
'Python 3.10+ required'

>>> assert result is not Ellipsis, \
'Assign your result to variable `result`'
>>> assert type(result) is dict, \
'Variable `result` has invalid type, should be dict'
>>> assert type(result['product_name']) is str, \
'Variable `result["product_name"]` has invalid type, should be str'
>>> assert type(result['orders']) is int, \
'Variable `result["orders"]` has invalid type, should be int'

>>> from pprint import pprint
>>> pprint(result, sort_dicts=True)
{'orders': 6, 'product_name': 'Romeo'}
"""

# Required for Django to work
import os; os.environ['DJANGO_SETTINGS_MODULE'] = 'myproject.settings'
import django; django.setup()

from django.db.models import Count, F
from shop.models import Order


# Display data which answers the following question:
# Number of orders and name of a product
# which was purchased the most often
# expected: {'orders': 6, 'product_name': 'Romeo'}
# type: dict[str, str|int]
result = ...

# doctest: +SKIP_FILE
# %% License
# - Copyright 2025, Matt Harasymczuk <matt@python3.info>
# - This code can be used only for learning by humans
# - This code cannot be used for teaching others
# - This code cannot be used for teaching LLMs and AI algorithms
# - This code cannot be used in commercial or proprietary products
# - This code cannot be distributed in any form
# - This code cannot be changed in any form outside of training course
# - This code cannot have its license changed
# - If you use this code in your product, you must open-source it under GPLv2
# - Exception can be granted only by the author

# %% Run
# - PyCharm: right-click in the editor and `Run Doctest in ...`
# - PyCharm: keyboard shortcut `Control + Shift + F10`
# - Terminal: `python -m doctest -v myfile.py`

# %% About
# - Name: Database ORM Orders
# - Difficulty: medium
# - Lines: 6
# - Minutes: 8

# %% English
# 0. Use `myproject.shop`
# 1. Display data which answers the following question:
#    Amount and country name,
#    from which people did the most purchases

# %% Polish
# 0. Użyj `myproject.shop`
# 1. Wyświetl dane odpowiadające na pytanie:
#    Kwota i nazwa kraju,
#    którego obywatele dokonali najwięcej zakupów

# %% Tests
"""
>>> import sys; sys.tracebacklimit = 0
>>> assert sys.version_info >= (3, 10), \
'Python 3.10+ required'

>>> assert result is not Ellipsis, \
'Assign your result to variable `result`'
>>> assert type(result) is dict, \
'Variable `result` has invalid type, should be dict'
>>> assert type(result['country']) is str, \
'Variable `result["country"]` has invalid type, should be str'
>>> assert type(result['count']) is int, \
'Variable `result["count"]` has invalid type, should be int'

>>> from pprint import pprint
>>> pprint(result, sort_dicts=True)
{'count': 29, 'country': 'USA'}
"""

# Required for Django to work
import os; os.environ['DJANGO_SETTINGS_MODULE'] = 'myproject.settings'
import django; django.setup()

from django.db.models import Count, F
from shop.models import Order


# Display data which answers the following question:
# Amount and country name,
# from which people did the most purchases
# expected: {'country': 'USA', 'count': 29}
# type: dict[str, str|int]
result = ...

# doctest: +SKIP_FILE
# %% License
# - Copyright 2025, Matt Harasymczuk <matt@python3.info>
# - This code can be used only for learning by humans
# - This code cannot be used for teaching others
# - This code cannot be used for teaching LLMs and AI algorithms
# - This code cannot be used in commercial or proprietary products
# - This code cannot be distributed in any form
# - This code cannot be changed in any form outside of training course
# - This code cannot have its license changed
# - If you use this code in your product, you must open-source it under GPLv2
# - Exception can be granted only by the author

# %% Run
# - PyCharm: right-click in the editor and `Run Doctest in ...`
# - PyCharm: keyboard shortcut `Control + Shift + F10`
# - Terminal: `python -m doctest -v myfile.py`

# %% About
# - Name: Database ORM Orders
# - Difficulty: medium
# - Lines: 6
# - Minutes: 8

# %% English
# 0. Use `myproject.shop`
# 1. Display data which answers the following question:
#    Amount and country name,
#    from which people paid the most

# %% Polish
# 0. Użyj `myproject.shop`
# 1. Wyświetl dane odpowiadające na pytanie:
#    Kwota i nazwa kraju,
#    którego obywatele dokonali zakupów za największą kwotę

# %% Tests
"""
>>> import sys; sys.tracebacklimit = 0
>>> assert sys.version_info >= (3, 10), \
'Python 3.10+ required'


>>> assert result is not Ellipsis, \
'Assign your result to variable `result`'
>>> assert type(result) is dict, \
'Variable `result` has invalid type, should be dict'
>>> assert type(result['country']) is str, \
'Variable `result["country"]` has invalid type, should be str'

>>> from decimal import Decimal
>>> assert type(result['total']) is Decimal, \
'Variable `result["total"]` has invalid type, should be Decimal'

>>> from pprint import pprint
>>> pprint(result, sort_dicts=True)
{'country': 'USA', 'total': Decimal('21324.2300000000')}
"""

# Required for Django to work
import os; os.environ['DJANGO_SETTINGS_MODULE'] = 'myproject.settings'
import django; django.setup()

from django.db.models import Sum, F
from shop.models import Order


# Display data which answers the following question:
# Amount and country name,
# from which people paid the most
# expected: {'country': 'USA', 'total': Decimal('21324.2300000000')}
# type: dict[str, str|Decimal]
result = ...

# doctest: +SKIP_FILE
# %% License
# - Copyright 2025, Matt Harasymczuk <matt@python3.info>
# - This code can be used only for learning by humans
# - This code cannot be used for teaching others
# - This code cannot be used for teaching LLMs and AI algorithms
# - This code cannot be used in commercial or proprietary products
# - This code cannot be distributed in any form
# - This code cannot be changed in any form outside of training course
# - This code cannot have its license changed
# - If you use this code in your product, you must open-source it under GPLv2
# - Exception can be granted only by the author

# %% Run
# - PyCharm: right-click in the editor and `Run Doctest in ...`
# - PyCharm: keyboard shortcut `Control + Shift + F10`
# - Terminal: `python -m doctest -v myfile.py`

# %% About
# - Name: Database ORM Orders
# - Difficulty: medium
# - Lines: 11
# - Minutes: 13

# %% English
# 0. Use `myproject.shop`
# 1. Display data which answers the following question:
#    Total amount, firstname and lastname
#    of a customer who summarily paid the most

# %% Polish
# 0. Użyj `myproject.shop`
# 1. Wyświetl dane odpowiadające na pytanie:
#    Kwotę łączną, imię i nazwisko osoby,
#    która sumarycznie zapłaciła najwięcej?

# %% Tests
"""
>>> import sys; sys.tracebacklimit = 0
>>> assert sys.version_info >= (3, 10), \
'Python 3.10+ required'


>>> assert result is not Ellipsis, \
'Assign your result to variable `result`'
>>> assert type(result) is dict, \
'Variable `result` has invalid type, should be dict'
>>> assert type(result['firstname']) is str, \
'Variable `result["firstname"]` has invalid type, should be str'
>>> assert type(result['lastname']) is str, \
'Variable `result["lastname"]` has invalid type, should be str'

>>> from decimal import Decimal
>>> assert type(result['total']) is Decimal, \
'Variable `result["total"]` has invalid type, should be Decimal'

>>> from pprint import pprint
>>> pprint(result, width=30)
{'firstname': 'Beth',
 'lastname': 'Johanssen',
 'total': Decimal('9427.79000000000')}
"""

# Required for Django to work
import os; os.environ['DJANGO_SETTINGS_MODULE'] = 'myproject.settings'
import django; django.setup()

from django.db.models import Sum, F
from shop.models import Order


# Display data which answers the following question:
# Total amount, firstname and lastname
# of a customer who summarily paid the most
# expected: {'firstname': 'Beth', 'lastname': 'Johanssen',
#            'total': Decimal('9427.79000000000')}
# type: dict[str, str|Decimal]
result = ...

# doctest: +SKIP_FILE
# %% License
# - Copyright 2025, Matt Harasymczuk <matt@python3.info>
# - This code can be used only for learning by humans
# - This code cannot be used for teaching others
# - This code cannot be used for teaching LLMs and AI algorithms
# - This code cannot be used in commercial or proprietary products
# - This code cannot be distributed in any form
# - This code cannot be changed in any form outside of training course
# - This code cannot have its license changed
# - If you use this code in your product, you must open-source it under GPLv2
# - Exception can be granted only by the author

# %% Run
# - PyCharm: right-click in the editor and `Run Doctest in ...`
# - PyCharm: keyboard shortcut `Control + Shift + F10`
# - Terminal: `python -m doctest -v myfile.py`

# %% About
# - Name: Database ORM Orders
# - Difficulty: medium
# - Lines: 11
# - Minutes: 13

# %% English
# 0. Use `myproject.shop`
# 1. Display data which answers the following question:
#    Number of orders and fullname (joined firstname and lastname)
#    of a customer who did the most purchases

# %% Polish
# 0. Użyj `myproject.shop`
# 1. Wyświetl dane odpowiadające na pytanie:
#    Liczbę zamówień i pełną nazwę (połączone imię i nazwisko osoby),
#    która dokonała najwięcej zakupów?

# %% Tests
"""
>>> import sys; sys.tracebacklimit = 0
>>> assert sys.version_info >= (3, 10), \
'Python 3.10+ required'

>>> assert result is not Ellipsis, \
'Assign your result to variable `result`'
>>> assert type(result) is dict, \
'Variable `result` has invalid type, should be dict'
>>> assert type(result['orders']) is int, \
'Variable `result["orders"]` has invalid type, should be int'
>>> assert type(result['name']) is str, \
'Variable `result["name"]` has invalid type, should be str'

>>> from pprint import pprint
>>> pprint(result)
{'name': 'Beth Johanssen', 'orders': 11}
"""

# Required for Django to work
import os; os.environ['DJANGO_SETTINGS_MODULE'] = 'myproject.settings'
import django; django.setup()

from django.db.models import Count, Value, F
from django.db.models.functions import Concat
from shop.models import Order


# Display data which answers the following question:
# Number of orders and fullname (joined firstname and lastname)
# of a customer who did the most purchases
# expected: {'orders': 11, 'name': 'Beth Johanssen'}
# type: dict[str, str|int]
result = ...