Elite Dangerous
From SQLZoo
station(id, max_landing_pad_size, has_blackmarket, has_commodities, updated_at, has_outfitting, government, state, system_id, has_shipyard, type, faction, has_rearm, allegiance, has_refuel, name, distance_to_star, has_repair) listing(station, commodity, supply, demand, buy_price, sell_price, update_count) commodity(name, average_price, category) economy(station, name) banned(station, commodity)
Queries useful to traders:
Where can I find Palladium near Minkinn system?
- dts is distance to star (don't bother if it is more than 1000 light seconds away)
- dist is the distance in light years - roughly one hop per 10ly
- reliability is the number of uploads - don't bother with 1
- 14 is roughly the radius of the search in light years
SELECT r.name,station.name,commodity,listing.buy_price,
station.distance_to_star dts,max_landing_pad_size l,
ROUND(SQRT((r.x-h.x)*(r.x-h.x)+(r.y-h.y)*(r.y-h.y)+(r.z-h.z)*(r.z-h.z))) dist,
update_count reliability
FROM system r JOIN station ON system_id=r.id
JOIN listing ON listing.station=station.id
JOIN (SELECT x,y,z,14 d FROM system WHERE name='Mikinn') h ON (1=1)
WHERE r.x BETWEEN h.x-d AND h.x+d
AND r.y BETWEEN h.y-d AND h.y+d
AND r.z BETWEEN h.z-d AND h.z+d
AND commodity='Palladium' AND buy_price>0
AND listing.update_count>1
AND station.distance_to_star<1000
ORDER BY listing.buy_price ASC
Where to sell Palladium near Brani
SELECT r.name,station.name,commodity,listing.sell_price,station.distance_to_star dts,max_landing_pad_size l
FROM system r JOIN station ON system_id=r.id
JOIN listing ON listing.station=station.id
JOIN (SELECT x,y,z, 20 d FROM system WHERE name='Brani') h ON (1=1)
WHERE r.x BETWEEN h.x-d AND h.x+d
AND r.y BETWEEN h.y-d AND h.y+d
AND r.z BETWEEN h.z-d AND h.z+d
AND commodity='Palladium' AND buy_price=0
AND listing.update_count>1
ORDER BY listing.sell_price DESC
Need to find a blackmarket near Kitche
SELECT r.name,station.name,station.distance_to_star dts,max_landing_pad_size l
FROM system r JOIN station ON system_id=r.id
JOIN (SELECT x,y,z, 20 d FROM system WHERE name='Kitche') h ON (1=1)
WHERE r.x BETWEEN h.x-d AND h.x+d
AND r.y BETWEEN h.y-d AND h.y+d
AND r.z BETWEEN h.z-d AND h.z+d
AND station.has_blackmarket>0
What to take if you are travelling from A to B
In this case from Mikinn, Wallace Dock to Brani, Virtanen Hub.
SELECT fl.commodity,fl.buy_price buy,tl.sell_price sell,tl.sell_price-fl.buy_price profit
FROM system ty JOIN station tt ON ty.id=tt.system_id AND ty.name='Brani'
AND tt.name='Virtanen Hub'
JOIN listing tl ON tl.station=tt.id
JOIN system fy JOIN station ft ON fy.id=ft.system_id AND fy.name='Mikinn'
AND ft.name='Wallace Dock'
JOIN listing fl ON fl.station=ft.id AND fl.commodity=tl.commodity
WHERE fl.buy_price>0 AND tl.sell_price>0
ORDER BY tl.sell_price-fl.buy_price DESC