Real World Example

Marketplace seller panels (Meesho, Amazon Seller Central, Flipkart Seller Hub) show revenue per product and orders per day. Prisma's groupBy and aggregate handle this without raw SQL; raw SQL is still available with $queryRaw for complex reports. (This assumes Product has a sellerId field linking it to a seller.)

src/sellers/seller-stats.service.ts

import { Injectable } from '@nestjs/common';
import { Prisma } from '@prisma/client';
import { PrismaService } from '../prisma/prisma.service';

@Injectable()
export class SellerStatsService {
  constructor(private readonly prisma: PrismaService) {}

  async summary(sellerId: number, from: Date) {
    const [top, totals, daily] = await Promise.all([
      this.prisma.orderItem.groupBy({
        by: ['productId'],
        where: { product: { sellerId }, order: { createdAt: { gte: from }, status: { not: 'CANCELLED' } } },
        _sum: { quantity: true },
        orderBy: { _sum: { quantity: 'desc' } },
        take: 5,
      }),
      this.prisma.order.aggregate({
        where: { items: { some: { product: { sellerId } } }, createdAt: { gte: from } },
        _count: true,
        _sum: { total: true },
      }),
      this.prisma.$queryRaw<{ day: Date; orders: bigint }[]>(Prisma.sql`
        SELECT date_trunc('day', o."createdAt") AS day, COUNT(DISTINCT o.id) AS orders
        FROM "Order" o JOIN "OrderItem" i ON i."orderId" = o.id JOIN "Product" p ON p.id = i."productId"
        WHERE p."sellerId" = ${sellerId} AND o."createdAt" >= ${from}
        GROUP BY 1 ORDER BY 1`),
    ]);
    return { top, orders: totals._count, revenue: totals._sum.total, daily: daily.map((d) => ({ ...d, orders: Number(d.orders) })) };
  }
}