Plotting multiple timeseries from group-by

[holypegasus]

Thanks a lot for creating this nifty plugin. One issue I haven't been able to overcome after spending a couple hours testing queries & searching online: given data with columns symbol, price, time where symbol comprises say 5 different stocks, I haven't been able to plot different colored/legended timeseries via the normal SELECT...GROUP BY symbol. Instead all data-points seem interpreted as one single timeseries with mistaken interpolations at the boundaries. Is this a known issue or am I not querying this correctly?

Thanks in advance!

[fr-ser]

Could you provide a sample database (CTE would also be fine 😉 ) as well as the SQL query?

[holypegasus]

Thanks for the quick reply. Data is based off https://github.com/vega/vega-datasets/blob/master/data/stocks.csv with date re-formatted as YYYY-MM-DD. Current query is

SELECT
  date || 'T00:00:00Z' AS time,
  price AS value,
  symbol
FROM stocks
GROUP BY symbol, time

[holypegasus]

Oddly, an analogous query on another data-source actually grouped-by as expected 🤷‍♂️ Nevertheless still curious if you see same issue on the original.

[fr-ser]

Maybe you just miss the ORDER BY time? You need to order by time for a useful time series. With the query below I got a normal time series chart. Is this what you expected?

WITH stocks(symbol, date, price) AS (VALUES
('MSFT', '2020-12-12', 39.81), ('AMZN', '2020-12-12', 77.9),
('MSFT', '2020-12-13', 41.1), ('AMZN', '2020-12-13', 75.6),
('MSFT', '2020-12-14', 38.2), ('AMZN', '2020-12-14', 88.2)
) 
SELECT
  date || 'T00:00:00Z' AS time,
  price AS value,
  symbol
FROM stocks
GROUP BY symbol, time
ORDER BY 1

[holypegasus]

Right so I did add ORDER BY time to the analogous query. The other thing that's required is switching the query type from Table to Time Series.

Pardon my false alarm - thanks again for your help!