Elite Dangerous

From SQLZoo
Jump to navigation Jump to search
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
DataWars, Data Science Practice Projects - LogoDataWars: Practice Data Science/Analysis with +100 Real Life Projects