Average value per unit, without dividing by zero

SQLHard13 min

Your task

For each warehouse, return city and avg_unit_cents: the total stock value divided by the total quantity, rounded to a whole number. A warehouse holding zero units returns NULL rather than failing. Order by city.

Tables

warehouse
idINTEGERcityTEXTcountryTEXT
1RotterdamNL
2MemphisUS
3OsakaJP
item
idINTEGERskuTEXTnameTEXTunit_centsINTEGER
1KB-1Keyboard4900
2MS-2Mouse2500
3MN-3Monitor19900
4CB-4Cable900
stock
warehouse_idINTEGERitem_idINTEGERquantityINTEGER
1140
120
1312
215
24300
337
More rows not shown