Extended ad system

[TobiGr]

I used InstaHub to repeat SQL queries in my class. I realized that the current DB schema is missing columns on which students can perform aggregate functions like SUM and AVG as well as simple mathematical calculations. I extended the ads entity for that reason and added four columns:

• customer: the person that created the advertisement and paid for it.
• price_per_click: the amount of money that is substracted from the budget when a user clicks on the ad
• budget: the money / budget that is remaining for this ad
• clicks: how often users clicked on this ad

This allows users to group by either the customer or the ad type to get some statistical data about the ads, e.g. the average price or the cumulative number of clicks on ads or the budget by type or customer. Additionally, the students can perform simple mathematical queries which e.g. return the original budget of an ad....

SELECT customer, SUM(clicks) as 'number of clicks' FROM ads GROUP BY customer;
SELECT type, AVG(price_per_click) as 'average price per click' FROM ads GROUP BY type;
SELECT name, (budget + price_per_click * clicks) as 'money paid' FROM ads;

I added those columns and filled them manually. If you are interested in the extended ad scheme, I can work on integrating those columns (migrations, editors, etc.).

Additionally, the algorithm that decides which ads are shown can be modified to inclide the condition ad.budget - ad.price_per_click >= 0

[wi-wissen]

I really like your idea. In fact, you can't aggregate that much right now (https://wi-wissen.github.io/instahub-doc-de/#/exercices?id=group-by). The difficult thing is that there are now some task collections also in books that somehow have to remain compatible with changes. One idea would be to include some kind of versioning in the hubs.

About your concrete idea:

• customer is perhaps difficult, because missspellings may be happen? But to bind this directly to a user as a foreign key is perhaps also confusing?
• price_per_click - this is the column priority which does not represent the maps this just without money?
• clicks / budget - do you think we should reduce this didactically? The idea is nice. Probably an extra counting table would be too complex here. One would then filter away non-clickable ads during ad selection?