Let’s say there are 7k objects in the database that have a pair field and only 1k unique pair fields, respectively, each field is duplicated 7 times. The task is to return an array of 1000 objects from 7k fields, filtered by the minimum price among all fields with the same pair field. access to the database must be done through prisma.js. but an sql query will be also fine. example:
that is how fields looks in db:
[ { pair: 'btcusdt', price: 20, createdAt: 2020-01-01, }, { pair: 'btcusdt', price: 2, createdAt: 2020-01-03, }, { pair: 'btcusdt', price: 1, createdAt: 2020-01-02, }, { pair: 'ethusdt', price: 40, createdAt: 2020-01-03, }, { pair: 'ethusdt', price: 2, createdAt: 2020-01-21, }, { pair: 'ethusdt', price: 9, createdAt: 2020-01-12, }, { pair: 'bnbusdt', price: 1, createdAt: 2020-01-1, }, { pair: 'bnbusdt', price: 0.6, createdAt: 2020-01-22, }, { pair: 'bnbusdt', price: 0.01, createdAt: 2020-01-03, }, ]
the problem is that this request returned fields sorted by price but also by createdAt
[ { pair: 'btcusdt', minPrice: 1, createdAt: 2020-01-02, }, { pair: 'ethusdt', price: 2, createdAt: 2020-01-21, }, { pair: 'bnbusdt', price: 0.01, createdAt: 2020-01-03, }, ]
const maxPrices = await prismaClient[exchange].groupBy({ by: ["pair"], _max: { price: true, createdAt: true, }, where: { createdAt: { gt: new Date(Date.now() - 1000 * 60 * dumpPeriod), }, }, });