Exercise 128
For each existing pair of buy-back centers from different tables (outcome and outcome_o) having the same identifier, determine the one with a greater total daily payout for each date at least one member of the pair collected recyclables.
Result set: buy-back center ID, date, one of the following messages:
“once a day”, if the center with only one payment per day possible has a greater payout;
“more than once a day”, if the payout is greater for the center with several transactions per day possible;
“both”, if both pair members paid out the same sum.
The main difficulty in understanding the formulation of this task is performing the comparison when one of the tables has records for a given day while the other doesn’t.
As stated in the subject area description, tables with the suffix “_o” in their names and without it belong to different database schemas. Thus, we can assume they describe the operations of different companies.
Since points with identical numbers are compared, the case of a point present in one table but not in its counterpart should be excluded from the result. As one of the companies doesn’t have such a point, we can’t compare something with a non-existent entity. Like, if only one runner participated in a race, it can’t be said they were better than the other.
We have an entirely different situation if there are no records for a point for a given day, but the point exists (i.e. it has records related to other days). This can be interpreted as the respective point was just closed or worked unproductive on that day. In this case, it is logical to award the win to its counterpart from the other company. Returning to our example with the runners, we can say with certainty the one finishing first performed better than the one dropping out of the race.
If you see the message about too many rows returned when checking your query on the site this could be just because of a comparison with a non-existent point.