Top n by group - postgres

Postgres set-up

library(DBI)
library(RPostgres)
library(jsonlite)

# Database connection
config <- jsonlite::read_json('C:/Users/brent/Documents/VS_Code/postgres/postgres/config.json')

con <- DBI::dbConnect(
  RPostgres::Postgres(),
  host      = 'localhost',
  port      = '5432',
  dbname    = 'stock_master_test',
  user      = 'postgres',
  password  = config$pg_password
)

Top n by group

with data as 
  (
    select 
    ra.symbol
    ,ra.date_stamp
    ,ra.symbol
    ,ra.sector
    ,fa.asset_growth
    ,fa.roa
    ,fa.roe
    ,fa.leverage
    ,fa.ttm_earnings
    from access_layer.return_attributes ra 
    inner join access_layer.fundamental_attributes fa
    on ra.symbol = fa.ticker
    and ra.date_stamp = fa.date_stamp
    where ra.date_stamp = '2021-06-30'
  )

select t1.* 
from 
  (
    select 
    data.*, 
    rank() over (partition by sector order by ttm_earnings desc) as rnk
    from data
  ) t1
where rnk <= 5;

Displaying records 1 - 10

symbol	date_stamp	symbol..3	sector	asset_growth	roa	roe	leverage	ttm_earnings	rnk
GE	2021-06-30	GE	1	-0.0033934	0.0431123	-0.3204940	0.8537238	11143	1
LMT	2021-06-30	LMT	1	-0.0012408	0.1378388	-1.0000000	0.8809308	6833	2
UNP	2021-06-30	UNP	1	-0.0206548	0.0852958	-0.3156572	0.7282285	5349	3
HON	2021-06-30	HON	1	0.0177595	0.0790414	-0.2671902	0.7245533	4865	4
DE	2021-06-30	DE	1	0.0051804	0.0458775	-0.2713793	0.8133810	3459	5
AAPL	2021-06-30	AAPL	2	0.0931371	0.1929899	-0.8597341	0.8129551	63930	1
MSFT	2021-06-30	MSFT	2	0.0104186	0.1739685	-0.4300619	0.5717851	51310	2
FB	2021-06-30	FB	2	0.0879491	0.2031955	-0.2589255	0.1947450	29146	3
INTC	2021-06-30	INTC	2	0.0539030	0.1421459	-0.2669366	0.4706547	20899	4
CSCO	2021-06-30	CSCO	2	0.0062945	0.1083833	-0.2716438	0.5907888	10129	5