I am trying to create a price tracker for learning purposes, very similar to what coinmarketcap does. I make an update call which gathers the info from each of the saved exchanges api's and store the results in the database every 5 minutes. I was wondering if there was a way to setup a find query that would calculate a volume weighted average price based on the database structure below (omitted obvious columns such as id and created/modified timestamps)
I currently obtain vwap by calculating the BTC/USD vwap price and store that. Than I convert any marktes that are quoted in BTC to USD using the price from the BTC/USD calculation. I then repeat for ETH/USD and so on. I'm wondering if I should rethink my database structure is there a way to build a query that would calculate vwaps in different quote currencies that I can than use to convert each market into other quote currencies.
Note: volume has 2 columns quote_volume and base_volume but has been condensed to volume for brevity.
Associations:
- Exchanges hasMany Markets
- Markets hasMany Prices
- Markets hasMany Quotes
- Prices hasMany PriceHistories
ExchangesTable
|------------|-------------|
| name | website |
|------------|-------------|
| Simex | www |
|------------|-------------|
MarketsTable
|---------------|------------|-------------|-------------|
| exchange_id | name | quote | base |
|---------------|------------|-------------|-------------|
| 12 | BTCUSD | USD | BTC |
|---------------|------------|-------------|-------------|
PricesTable
|---------------|-----------|------------|------------|-------------|
| market_id | last | high | low | volume |
|---------------|-----------|------------|------------|-------------|
| 5 | 3989.36 | 4012.78 | 3942.87 | 125,000,000 |
|---------------|-----------|------------|------------|-------------|
PriceHistoriesTable
|---------------|-----------|------------|------------|-------------|
| prices_id | last | high | low | volume |
|---------------|-----------|------------|------------|-------------|
| 8 | 3989.36 | 4012.78 | 3942.87 | 125,000,000 |
|---------------|-----------|------------|------------|-------------|
