PaulieScanlon

import type { FunctionComponent } from 'react';
import type { AnalyticsType } from '../../schema';

type PickedProps = Pick<AnalyticsType, 'city' | 'country' | 'flag'>;

interface Props {
  analyticsData: PickedProps[];
}

const VisitorsList: FunctionComponent<Props> = ({ analyticsData }) => {

PaulieScanlon

<VisitorsList analyticsData={results} />

PaulieScanlon

const results = await db
  .select({
    city: analytics.city,
    country: analytics.country,
    flag: analytics.flag,
  })
  .from(analytics)
  .where(eq(analytics.country, 'US'))
  .orderBy(analytics.date)
  .limit(10);

PaulieScanlon

import { pgTable, serial, varchar, timestamp, decimal} from 'drizzle-orm/pg-core';

+ import { type InferSelectModel } from 'drizzle-orm';

export const analytics = pgTable('analytics', {
  id: serial('id').primaryKey(),
  date: timestamp('date').notNull(),
  slug: varchar('slug', { length: 255 }).notNull(),
  referrer: varchar('referrer', { length: 255 }),
  flag: varchar('flag', { length: 16 }),

PaulieScanlon

- import { pgTable, serial, varchar } from 'drizzle-orm/pg-core';
+ import { pgTable, serial, varchar, date } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  user_id: serial('user_id').primaryKey(),
  first_name: varchar('first_name', { length: 100 }),
  last_name: varchar('last_name', { length: 100 }),
  email: varchar('email', { length: 80 }).unique(),
  role: varchar('role', { length: 20 }),
+  date_of_birth: date('date_of_birth')

PaulieScanlon

.select({
  city: analytics.city,
  country: analytics.country,
  flag: analytics.flag,
  count: sql`COUNT(${reactions.id})`.as('count'),
})
.from(analytics)
.innerJoin(reactions, eq(analytics.slug, reactions.slug))
.where(and(eq(reactions.reaction, 'happy'), gte(analytics.date, new Date(new Date().getTime() - 30 * 24 * 60 * 60 * 1000))))
.groupBy(analytics.city, analytics.country, analytics.flag)

PaulieScanlon

WITH happy_reactions AS (
  SELECT analytics.city, analytics.country, analytics.flag, COUNT(reactions.id) AS count
  FROM analytics
  INNER JOIN reactions ON analytics.slug = reactions.slug
  WHERE reactions.reaction = 'happy' AND analytics.date >= CURRENT_DATE - INTERVAL '30' DAY
  GROUP BY analytics.city, analytics.country, analytics.flag
)
SELECT city, country, flag, count
FROM happy_reactions
ORDER BY count DESC

PaulieScanlon

.select({
  id: orders.order_id,
  order_date: orders.order_date,
  first_name: orders.first_name,
  last_name: orders.last_name,
})
.from(orders)
.innerJoin(customers, eq(orders.order_id, customers.customer_id))
.limit(50);

PaulieScanlon

SELECT 
  orders.order_id,
  orders.order_date,
  orders.first_name,
  orders.last_name
FROM orders
INNER JOIN customers ON orders.order_id = customers.customer_id
LIMIT 50;

PaulieScanlon

.select({
  id: users.user_id,
  first_name: users.first_name,
  last_name: users.last_name,
  email: users.email,
  role: users.role,
})
.from(users)
.where(eq(users.role, 'admin'))
.orderBy(users.last_name);