Problem Set 6

This problem set must be submitted on Canvas by 11:59 PM PST on May 15, 2024.

Important

All of the problems in this assignment require your functions to output a string. This will be a syntactically valid SQL query for the schema of the tables in the auctions.db file that is on JupyterHub and the course website.

Note

The two files above may not contain the same set of auctions – this will not matter for your solutions, since you’re only writing queries to query a database that looks like one of these files.

Exercise 0

Please write a function that takes no arguments and returns a link to your solutions on GitHub.

Use the following shell:

def github() -> str:
    """
    Some docstrings.
    """

    return "https://github.com/<user>/<repo>/blob/main/<filename.py>"

Exercise 1

Please write a function called std that takes no arguments and returns a string containing a SQL query that can be run against the auctions.db database that outputs a table that has two columns: itemId and std, the standard deviation of bids for that item. Include only bids for which the unbiased standard deviation can be calculated (that is, those with at least two bids). Calculate standard deviation as

\[ s={\sqrt {\frac {\sum _{i=1}^{n}(x_{i}-{\overline {x}})^{2}}{n-1}}} \]

Use the following shell:

def std() -> str:
    """
    Some docstrings.
    """

    return None

Exercise 2

Please write a function called bidder_spend_frac that takes no arguments and returns a string containing a SQL query that can be run against the auctions.db database that outputs a table that has four columns:

• bidderName: the name of the bidder
• total_spend: the amount the bidder spent (that is, the sum of their winning bids)
• total_bids: the amount the bidder bid, regardless of the outcome. NB: bidders may submit multiple bids for an item – if this is the case only count their highest bid for an item for this calculation.
• spend_frac: total_spend/total_bids

Use the following shell:

def bidder_spend_frac() -> str:
    """
    Some docstrings.
    """

    return None

Exercise 3

Please write a function called min_increment_freq that takes no arguments and returns a string containing a SQL query that can be run against the auctions.db database that outputs a table that has one column (freq) which represents the fraction of bids in the database that are exactly the minimum bid increment (items.bidIncrement) above the previous high bid. For this exercise, exclude items where isBuyNowUsed=1.

Use the following shell:

def min_increment_freq() -> str:
    """
    Some docstrings.
    """

    return None

Exercise 4

Please write a function called win_perc_by_timestamp that takes no arguments and returns a string containing a SQL query that can be run against the auctions.db database that outputs a table that has two columns:

• timestamp_bin: Using the same methodology as in the slides to normalize the percentage of time remaining in the auction when a bid is placed, normalize the bid timestamp and classify it as one of ten bins: 1 corresponds to 0-.1, 2 corresponds to .1-.2, etc.
• win_perc: the frequency with which a bid placed with this timestamp bin won the auction.

Use the following shell:

def win_perc_by_timestamp() -> str:
    """
    Some docstrings.
    """

    return None