Run Queries on Data

Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

Query 1: Finds the most traded stocks

This query finds the most traded stocks.

SQL

USE trades;

SELECT stock_symbol, COUNT(*) AS c
    FROM trade
    GROUP BY stock_symbol
    ORDER BY c DESC LIMIT 5;

+--------------+-------+
| stock_symbol | c     |
+--------------+-------+
| TIG          | 30401 |
| FB           | 30300 |
| MNGA         | 30300 |
| QCOM         | 30199 |
| KHC          | 30199 |
+--------------+-------+
5 rows in set (0.11 sec)

Query 2: Finds the most volatile stocks

This query finds the most volatile stocks which have the highest variance in prices.

SQL

USE trades;

SELECT stock_symbol, VARIANCE(share_price) var
    FROM trade
    GROUP BY stock_symbol
    ORDER BY var DESC 
    LIMIT 5; /* Remove LIMIT to see full result*/

+--------------+-------------+
| stock_symbol | var         |
+--------------+-------------+
| IBKR         | 32.24672973 |
| XRAY         | 28.09745295 |
| ODFL         | 25.18033964 |
| ALGN         | 24.47217863 |
| VXUS         | 24.11941421 |
+--------------+-------------+
5 rows in set (1.74 sec)

Query 3: Portfolio Aggregation

This is a portfolio aggregation query that uses Common Table Expression (CTE), JOIN, and window functions. It also computes minimum, maximum, standard deviation, weighted average, and percentiles for each company stock.

SQL

USE trades;

WITH folio AS (
    SELECT id, stock_symbol, shares, share_price, trade_time
    FROM trade
  ),
AggCalcs AS (
    SELECT
        stock_symbol AS ACsymb,
        MAX(share_price) AS pmax,
        MIN(share_price) AS pmin,
        STD(share_price) AS pstd,
        SUM(share_price*shares)/SUM(shares) AS avg_pps,  ## Weighted Average
        SUM(share_price*shares) AS total_pvalue
    FROM trade
    GROUP BY 1
)
SELECT
    DISTINCT folio.stock_symbol,
    avg_pps,
    pmin,
    pmax,
    percentile_cont(.25) WITHIN group (ORDER BY share_price) OVER (PARTITION BY stock_symbol) AS Q1,
    percentile_cont(.5) WITHIN group (ORDER BY share_price) OVER (PARTITION BY stock_symbol)  AS median,
    percentile_cont(.75) WITHIN group (ORDER BY share_price) OVER (PARTITION BY stock_symbol) AS Q3
FROM folio
JOIN AggCalcs ON (folio.stock_symbol = ACsymb)
ORDER BY folio.stock_symbol
LIMIT 5; /* Remove LIMIT to see full result*/

+--------------+-----------------+---------+---------+-------------+-------------+-------------+
| stock_symbol | avg_pps         | pmin    | pmax    | Q1          | median      | Q3          |
+--------------+-----------------+---------+---------+-------------+-------------+-------------+
| AABA         | 90.104679207921 | 86.7900 | 93.5923 | 88.89430000 | 90.02250000 | 91.32900000 |
| AAL          | 81.627300000000 | 78.1108 | 85.8548 | 79.65410000 | 80.57210000 | 84.51420000 |
| AAPL         | 74.203350495050 | 71.3886 | 77.0869 | 73.46370000 | 74.15330000 | 75.05420000 |
| ABMD         | 77.400400990099 | 70.7616 | 85.1131 | 73.57460000 | 76.65900000 | 80.89240000 |
| ACGL         | 73.807070297030 | 70.4365 | 79.1696 | 72.21950000 | 73.09320000 | 75.56110000 |
+--------------+-----------------+---------+---------+-------------+-------------+-------------+
5 rows in set (3.78 sec)