Get symbols that have had their price increase within the past 6 month by 5 percent

Question

I have the following two tables which track stock prices daily

CREATE TABLE "public"."security" (
    "symbol" text NOT NULL,
    "security" text NOT NULL
    CONSTRAINT "security_pk" PRIMARY KEY ("symbol")
)

and the following table which keeps track of the security prices daily

CREATE TABLE "public"."security_data" (
    "ticker" text NOT NULL,
    "time" date NOT NULL,
    "low" numeric NOT NULL,
    "high" numeric NOT NULL,
    "open" numeric NOT NULL,
    "close" numeric NOT NULL,
    "volume" double precision,
    CONSTRAINT "security_data_pkey" PRIMARY KEY ("ticker", "time"),
    CONSTRAINT "security_data_ticker_fkey" FOREIGN KEY (ticker) REFERENCES security(symbol) ON UPDATE CASCADE ON DELETE CASCADE NOT DEFERRABLE
)

I want to come up with a SQL query that returns a list of "symbol" for stocks that had their average price for the past week higher by more than 5% than the average price for the past 6 months. I can't figure out the best way to write this query. Is it possible to write this in one query? If so how?

GMB GMB · Accepted Answer · 2020-09-27T21:41:37

You can use aggregation. Assuming that you column close represents the price, you can do:

select ticker
from security_date
where time > current_date - '6 month'::interval
group by ticker
having avg(close) filter(where time > current_date - '1 week'::interval) 
    > 1.05 * avg(close)

It doesn't look like you need the "live" table to get the result you want.

Get symbols that have had their price increase within the past 6 month by 5 percent

1 Answers