This guide contains simple examples of queries you can make to the Reporting DB to receive data on essential statistics and operations. The examples are sorted by the type of server entity that they cover.
In the database, some fields contain numerical identifiers of the corresponding enum values from the Reporting API proto files. E.g., this is the case for the
deal_statuscolumn in the
See the 10 commission profiles with the largest minimum commissions.
SELECT commission_profile_id, name, min_commission FROM "tutorialdb_demo"."commission_profile" ORDER BY min_commission DESC LIMIT 10;
See all groups in which traders can change stop out types and fair stop outs are disabled.
SELECT trader_group_id, name FROM "tutorialdb_demo"."trader_group" WHERE fair_stop_out = false AND allow_trader_change_so = true;
See the last 1000 deal IDs (and the related symbol IDs) whose status equaled
SELECT deal_id, symbol_id FROM "tutorialdb_demo"."deal" WHERE deal_status = 6 ORDER BY deal_id DESC LIMIT 1000;
See 100 positions with the largest used margin that were open by the start of the current day.
SELECT trader_id, position_id, symbol_id, margin, net_unrealized_pnl FROM "tutorialdb_demo"."daily_open_positions" ORDER BY margin DESC LIMIT 100;
See the top 10 symbols for the volume of all currently open positions is the largest.
SELECT s.symbol_id, name, description, SUM(d.volume) AS total_volume FROM "tutorialdb_demo"."symbol" s INNER JOIN "tutorialdb_demo"."daily_open_positions" d ON s.symbol_id = d.symbol_id GROUP BY s.symbol_id, name, description ORDER BY total_volume DESC LIMIT 10;
See 10 traders with the largest negative balance deltas who currently have open positions.
SELECT b.trader_id, SUM(b.delta) AS total_delta FROM "tutorialdb_demo"."balance_history" b INNER JOIN "tutorialdb_demo"."daily_open_positions" o ON b.trader_id = o.trader_id GROUP BY b.trader_id ORDER BY total_delta ASC LIMIT 10;