Paraguayan Banking Deals

CREATE TABLE DEALS (
  DEAL_ID                     NUMBER(38,0)  NOT NULL,
  DEAL_KEY                    VARCHAR       NOT NULL,
  BANK_ID                     NUMBER(38,0),
  BUSINESS_ID                 NUMBER(38,0),
  BUSINESS_NAME_AS_PUBLISHED  VARCHAR,
  DEAL_TYPE                   VARCHAR       NOT NULL,
  DESCRIPTION                 VARCHAR       NOT NULL,
  CONDITIONS_TEXT             VARCHAR,
  TERMS_TEXT                  VARCHAR,
  PERCENT_OFF                 FLOAT,
  AMOUNT_OFF                  NUMBER(38,0),
  PERCENT_BACK                FLOAT,
  AMOUNT_BACK                 NUMBER(38,0),
  INSTALLMENTS                NUMBER(38,0),
  POINTS_MULTIPLIER           FLOAT,
  FIXED_POINTS                NUMBER(38,0),
  MAX_UNITS                   NUMBER(38,0),
  GIFT                        VARCHAR,
  PRICE                       NUMBER(38,0),
  LIST_PRICE                  NUMBER(38,0),
  SCOPE                       VARCHAR,
  SUMMARY                     VARCHAR,
  MIN_PURCHASE_AMOUNT         NUMBER(38,0),
  MAX_DISCOUNT_AMOUNT         NUMBER(38,0),
  MAX_PURCHASE_AMOUNT         NUMBER(38,0),
  MAX_REFUND_AMOUNT           NUMBER(38,0),
  CURRENCY                    VARCHAR,
  VALID_FROM                  DATE,
  VALID_UNTIL                 DATE,
  SCHEDULE_RULE               VARCHAR,
  DAYS_OF_WEEK                ARRAY,
  NTH_WEEKDAY                 NUMBER(38,0),
  MONTH_DAYS                  ARRAY,
  SCHEDULE_TEXT               VARCHAR,
  FIRST_SEEN_AT               TIMESTAMP_TZ  NOT NULL,
  LAST_SEEN_AT                TIMESTAMP_TZ  NOT NULL,
  RETIRED_AT                  TIMESTAMP_TZ,
  VERIFIED_AT                 TIMESTAMP_TZ,
  IS_PUBLISHED                BOOLEAN       NOT NULL
);

DEAL_ID NUMBER(38,0) · always filled

Identifies the deal. Stable across updates.

DEAL_KEY VARCHAR · always filled

A readable identifier, also stable across updates: bank, merchant, deal type and the promotion.

BANK_ID NUMBER(38,0)

The bank that offers the deal (BANKS). Null for a merchant's own deal, honoured whatever card pays. Joins BANKS

BUSINESS_ID NUMBER(38,0)

The merchant (BUSINESSES). Null when the deal applies to any purchase, such as a first-purchase deal. Joins BUSINESSES

BUSINESS_NAME_AS_PUBLISHED VARCHAR

The merchant's name as the bank's site printed it.

DEAL_TYPE VARCHAR · always filled

What the benefit is: discount, fixed_discount, cashback, fixed_cashback, installments, points, two_for_one, gift, special_price or other.

DESCRIPTION VARCHAR · always filled

The deal as the bank's site describes it, in Spanish.

CONDITIONS_TEXT VARCHAR

The conditions as the bank's site states them, in Spanish.

TERMS_TEXT VARCHAR

The terms and conditions as published, in Spanish.

PERCENT_OFF FLOAT

discount: the percentage taken off at the register. For a tiered deal, the best tier.

AMOUNT_OFF NUMBER(38,0)

fixed_discount: the amount taken off, in CURRENCY.

PERCENT_BACK FLOAT

cashback: the percentage credited back to the card statement. For a tiered deal, the best tier.

AMOUNT_BACK NUMBER(38,0)

fixed_cashback: the amount credited back, in CURRENCY.

INSTALLMENTS NUMBER(38,0)

installments: the number of interest-free installments.

POINTS_MULTIPLIER FLOAT

points: times the card's usual points rate, such as 3 for triple points.

FIXED_POINTS NUMBER(38,0)

points: a set number of points, given instead of a multiplier.

MAX_UNITS NUMBER(38,0)

two_for_one: how many times it applies to one purchase.

GIFT VARCHAR

gift: what is handed over, or the fee waived.

PRICE NUMBER(38,0)

special_price: the price, in CURRENCY.

LIST_PRICE NUMBER(38,0)

special_price: the usual price it compares to, in CURRENCY.

SCOPE VARCHAR

two_for_one and special_price: what the offer covers, when not the whole purchase.

SUMMARY VARCHAR

other: the benefit as published, for one that fits no other type.

MIN_PURCHASE_AMOUNT NUMBER(38,0)

The smallest purchase the deal applies to, in CURRENCY.

MAX_DISCOUNT_AMOUNT NUMBER(38,0)

discount: the most taken off one purchase. fixed_discount: the most taken off over the period the terms set. in CURRENCY.

MAX_PURCHASE_AMOUNT NUMBER(38,0)

cashback: the most spending the percentage is computed on, in CURRENCY.

MAX_REFUND_AMOUNT NUMBER(38,0)

cashback and fixed_cashback: the most credited back, in CURRENCY.

CURRENCY VARCHAR

ISO 4217 code of every amount in the row: PYG or USD. Null for a deal type with no amount.

VALID_FROM DATE

The first day the deal applies, when the bank states one.

VALID_UNTIL DATE

The last day the deal applies, inclusive, when the bank states one.

SCHEDULE_RULE VARCHAR

Which days it applies within its validity: daily; dow (DAYS_OF_WEEK); nth_dow (the NTH_WEEKDAY occurrence of DAYS_OF_WEEK in each month); month_days (MONTH_DAYS); unknown (none stated, read as daily). DEAL_DAYS lists the days.

DAYS_OF_WEEK ARRAY

ISO weekdays it applies on, 1 = Monday to 7 = Sunday.

NTH_WEEKDAY NUMBER(38,0)

For nth_dow: which occurrence in the month, 1 = first, -1 = last.

MONTH_DAYS ARRAY

For month_days: the days of the month it applies on.

SCHEDULE_TEXT VARCHAR

When the deal applies, in the bank's own words, in Spanish.

FIRST_SEEN_AT TIMESTAMP_TZ · always filled

When the deal was first found on the bank's site.

LAST_SEEN_AT TIMESTAMP_TZ · always filled

When the deal was last found on the bank's site.

RETIRED_AT TIMESTAMP_TZ

When the bank's site stopped publishing the deal. Null while it is still published.

VERIFIED_AT TIMESTAMP_TZ

When every value of the deal was last checked against the bank's own pages.

IS_PUBLISHED BOOLEAN · always filled

Whether the bank's site still published the deal at the latest update.

CREATE TABLE DEAL_DAYS (
  DEAL_ID  NUMBER(38,0)  NOT NULL,
  DATE     DATE          NOT NULL
);

DEAL_ID NUMBER(38,0) · always filled

The deal (DEALS). Joins DEALS

DATE DATE · always filled

A day the deal applies on.

CREATE TABLE BANKS (
  BANK_ID       NUMBER(38,0)  NOT NULL,
  SLUG          VARCHAR       NOT NULL,
  NAME          VARCHAR       NOT NULL,
  BANK_TYPE     VARCHAR       NOT NULL,
  BENEFITS_URL  VARCHAR
);

BANK_ID NUMBER(38,0) · always filled

Identifies the bank.

SLUG VARCHAR · always filled

A short, stable name for the bank.

NAME VARCHAR · always filled

The bank's name.

BANK_TYPE VARCHAR · always filled

bank, digital_bank, cooperative_bank, finance_company or credit_union.

BENEFITS_URL VARCHAR

The bank's page listing its deals.

CREATE TABLE BUSINESSES (
  BUSINESS_ID  NUMBER(38,0)  NOT NULL,
  SLUG         VARCHAR       NOT NULL,
  NAME         VARCHAR       NOT NULL
);

BUSINESS_ID NUMBER(38,0) · always filled

Identifies the merchant.

SLUG VARCHAR · always filled

A short, stable name for the merchant.

NAME VARCHAR · always filled

The merchant's name.

CREATE TABLE BUSINESS_CATEGORIES (
  BUSINESS_ID  NUMBER(38,0)  NOT NULL,
  CATEGORY     VARCHAR       NOT NULL
);

BUSINESS_ID NUMBER(38,0) · always filled

The merchant (BUSINESSES). Joins BUSINESSES

CATEGORY VARCHAR · always filled

The category, such as supermarket, dining, fashion_apparel or gas_station.

CREATE TABLE CARDS (
  CARD_ID    NUMBER(38,0)  NOT NULL,
  CARD_KEY   VARCHAR       NOT NULL,
  BANK_ID    NUMBER(38,0)  NOT NULL,
  CARD_TYPE  VARCHAR       NOT NULL,
  PROVIDER   VARCHAR       NOT NULL,
  LEVEL      VARCHAR       NOT NULL,
  NAME       VARCHAR       NOT NULL
);

CARD_ID NUMBER(38,0) · always filled

Identifies the card.

CARD_KEY VARCHAR · always filled

A readable identifier, stable across updates: bank, type, provider and product.

BANK_ID NUMBER(38,0) · always filled

The issuing bank (BANKS). Joins BANKS

CARD_TYPE VARCHAR · always filled

credit, debit or prepaid.

PROVIDER VARCHAR · always filled

The brand whose network carries the payment: visa, mastercard, dinelco, panal, cabal, amex, bancard_check or gourmet_card.

LEVEL VARCHAR · always filled

The tier: classic, gold, platinum, black, super_premium (issued only to a segment of the bank's customers) or unknown.

NAME VARCHAR · always filled

The product as the bank names it, such as Visa Infinite.

CREATE TABLE CARD_REQUIREMENTS (
  REQUIREMENT_ID      NUMBER(38,0)  NOT NULL,
  CARD_ID             NUMBER(38,0)  NOT NULL,
  REQUIREMENT_TYPE    VARCHAR       NOT NULL,
  MIN_MONTHLY_INCOME  NUMBER(38,0),
  CURRENCY            VARCHAR,
  MEMBERSHIP          VARCHAR,
  DESCRIPTION         VARCHAR
);

REQUIREMENT_ID NUMBER(38,0) · always filled

Identifies the requirement.

CARD_ID NUMBER(38,0) · always filled

The card (CARDS). Joins CARDS

REQUIREMENT_TYPE VARCHAR · always filled

income, membership or other.

MIN_MONTHLY_INCOME NUMBER(38,0)

income: the minimum monthly income, in CURRENCY.

CURRENCY VARCHAR

income: ISO 4217 code of the income.

MEMBERSHIP VARCHAR

membership: what the customer must belong to, such as a segment of the bank.

DESCRIPTION VARCHAR

membership and other: the condition as the bank explains it, in Spanish.

CREATE TABLE DEAL_CARDS (
  DEAL_ID  NUMBER(38,0)  NOT NULL,
  CARD_ID  NUMBER(38,0)  NOT NULL
);

DEAL_ID NUMBER(38,0) · always filled

The deal (DEALS). Joins DEALS

CARD_ID NUMBER(38,0) · always filled

A card it is valid with (CARDS). Joins CARDS

CREATE TABLE LIMITATIONS (
  LIMITATION_ID  NUMBER(38,0)  NOT NULL,
  CODE           VARCHAR       NOT NULL,
  LABEL          VARCHAR       NOT NULL,
  KIND           VARCHAR       NOT NULL,
  DESCRIPTION    VARCHAR       NOT NULL,
  BANK_ID        NUMBER(38,0)
);

LIMITATION_ID NUMBER(38,0) · always filled

Identifies the limitation.

CODE VARCHAR · always filled

A readable identifier, stable across updates.

LABEL VARCHAR · always filled

A short label, such as QR or Encarnación.

KIND VARCHAR · always filled

card, bank, card_class, channel, network, city, region, branch, loyalty_program_or_tier or unknown. Rows of different kinds all apply; included rows of one kind are alternatives.

DESCRIPTION VARCHAR · always filled

What the limitation means in practice.

BANK_ID NUMBER(38,0)

The bank whose lineup it belongs to (BANKS), when it is one bank's. Joins BANKS

CREATE TABLE DEAL_LIMITATIONS (
  DEAL_ID        NUMBER(38,0)  NOT NULL,
  LIMITATION_ID  NUMBER(38,0)  NOT NULL,
  EXCLUDED       BOOLEAN       NOT NULL
);

DEAL_ID NUMBER(38,0) · always filled

The deal (DEALS). Joins DEALS

LIMITATION_ID NUMBER(38,0) · always filled

A limitation on it (LIMITATIONS). Joins LIMITATIONS

EXCLUDED BOOLEAN · always filled

True when the limitation shuts out what it names, such as a card the deal does not accept.

CREATE TABLE UPDATES (
  UPDATED_AT  TIMESTAMP_TZ  NOT NULL,
  DATA_AS_OF  TIMESTAMP_TZ  NOT NULL
);

UPDATED_AT TIMESTAMP_TZ · always filled

When the update was published here.

DATA_AS_OF TIMESTAMP_TZ · always filled

When the version it published was recorded, after the crawl and checks it comes from.

Check how fresh the data is

UPDATES gains a row with every update. UPDATED_AT is when the update reached Snowflake; DATA_AS_OF is when the version it carries was recorded, after the crawl and the checks it comes from.

SELECT UPDATED_AT, DATA_AS_OF
FROM UPDATES
ORDER BY UPDATED_AT DESC
LIMIT 1;

List the deals that apply today

DEAL_DAYS expands every validity period and weekly or monthly schedule into dates, so the deals of any day are one filter. It also keeps the remaining days of retired deals, so filter on IS_PUBLISHED for what is on offer now.

SELECT
  b.NAME AS BANK,
  COALESCE(m.NAME, d.BUSINESS_NAME_AS_PUBLISHED) AS MERCHANT,
  d.DEAL_TYPE,
  d.DESCRIPTION
FROM DEAL_DAYS dd
JOIN DEALS d ON d.DEAL_ID = dd.DEAL_ID
LEFT JOIN BANKS b ON b.BANK_ID = d.BANK_ID
LEFT JOIN BUSINESSES m ON m.BUSINESS_ID = d.BUSINESS_ID
WHERE dd.DATE = CURRENT_DATE
  AND d.IS_PUBLISHED
ORDER BY BANK, MERCHANT;

Compare supermarket benefits for the next seven days

A deal fills only the benefit columns of its DEAL_TYPE, so the maximum of each benefit column puts discounts, cashback and interest-free installments side by side, per bank and day.

SELECT
  dd.DATE,
  b.NAME AS BANK,
  MAX(d.PERCENT_OFF) AS BEST_PERCENT_OFF,
  MAX(d.PERCENT_BACK) AS BEST_PERCENT_BACK,
  MAX(d.INSTALLMENTS) AS MOST_INSTALLMENTS
FROM DEAL_DAYS dd
JOIN DEALS d ON d.DEAL_ID = dd.DEAL_ID
JOIN BANKS b ON b.BANK_ID = d.BANK_ID
JOIN BUSINESS_CATEGORIES c ON c.BUSINESS_ID = d.BUSINESS_ID
WHERE c.CATEGORY = 'supermarket'
  AND d.IS_PUBLISHED
  AND dd.DATE BETWEEN CURRENT_DATE AND DATEADD(day, 6, CURRENT_DATE)
GROUP BY dd.DATE, b.NAME
ORDER BY dd.DATE, BEST_PERCENT_BACK DESC NULLS LAST;

Find today’s deals for one kind of card

DEAL_CARDS lists every card a deal is valid with. Filter CARDS on its type, network and tier, or on CARD_KEY for one product.

SELECT
  b.NAME AS BANK,
  c.NAME AS CARD,
  COALESCE(m.NAME, d.BUSINESS_NAME_AS_PUBLISHED) AS MERCHANT,
  d.DEAL_TYPE,
  d.DESCRIPTION
FROM DEAL_CARDS dc
JOIN CARDS c ON c.CARD_ID = dc.CARD_ID
JOIN BANKS b ON b.BANK_ID = c.BANK_ID
JOIN DEALS d ON d.DEAL_ID = dc.DEAL_ID
JOIN DEAL_DAYS dd ON dd.DEAL_ID = d.DEAL_ID
LEFT JOIN BUSINESSES m ON m.BUSINESS_ID = d.BUSINESS_ID
WHERE c.CARD_TYPE = 'credit'
  AND c.PROVIDER = 'visa'
  AND c.LEVEL = 'platinum'
  AND dd.DATE = CURRENT_DATE
  AND d.IS_PUBLISHED
ORDER BY BANK, CARD, MERCHANT;

Count the deals that can be paid by QR

A deal’s limitations of different kinds all apply, while its included limitations of one kind are alternatives: a deal limited to QR and to card-terminal payments takes either. EXCLUDED marks a limitation that shuts out what it names.

SELECT
  b.NAME AS BANK,
  COUNT(DISTINCT d.DEAL_ID) AS DEALS_PAYABLE_BY_QR
FROM DEAL_LIMITATIONS dl
JOIN LIMITATIONS l ON l.LIMITATION_ID = dl.LIMITATION_ID
JOIN DEALS d ON d.DEAL_ID = dl.DEAL_ID
JOIN BANKS b ON b.BANK_ID = d.BANK_ID
WHERE l.KIND = 'channel'
  AND l.LABEL ILIKE '%QR%'
  AND NOT dl.EXCLUDED
  AND d.IS_PUBLISHED
GROUP BY b.NAME
ORDER BY DEALS_PAYABLE_BY_QR DESC;

Compare the benefit mix of every bank

One row per bank: how many deals it publishes now, how they split by type, and its average cashback percentage.

SELECT
  b.NAME AS BANK,
  COUNT(*) AS PUBLISHED_DEALS,
  COUNT_IF(d.DEAL_TYPE = 'discount') AS DISCOUNTS,
  COUNT_IF(d.DEAL_TYPE = 'cashback') AS CASHBACK,
  COUNT_IF(d.DEAL_TYPE = 'installments') AS INSTALLMENTS,
  ROUND(AVG(d.PERCENT_BACK), 1) AS AVG_PERCENT_BACK
FROM DEALS d
JOIN BANKS b ON b.BANK_ID = d.BANK_ID
WHERE d.IS_PUBLISHED
GROUP BY b.NAME
ORDER BY PUBLISHED_DEALS DESC;

Rank credit cards by the income they require

CARD_REQUIREMENTS has one row per condition of a card, and all of a card’s conditions apply. Income requirements carry their amount and currency.

SELECT
  b.NAME AS BANK,
  c.NAME AS CARD,
  c.PROVIDER,
  c.LEVEL,
  r.MIN_MONTHLY_INCOME,
  r.CURRENCY
FROM CARD_REQUIREMENTS r
JOIN CARDS c ON c.CARD_ID = r.CARD_ID
JOIN BANKS b ON b.BANK_ID = c.BANK_ID
WHERE r.REQUIREMENT_TYPE = 'income'
  AND c.CARD_TYPE = 'credit'
ORDER BY r.MIN_MONTHLY_INCOME DESC;

Track the deals retired in the last 30 days

Retired deals stay in DEALS with the moment the bank’s site stopped publishing them, so history and churn are a filter on RETIRED_AT.

SELECT
  b.NAME AS BANK,
  COUNT(*) AS RETIRED_DEALS
FROM DEALS d
JOIN BANKS b ON b.BANK_ID = d.BANK_ID
WHERE d.RETIRED_AT >= DATEADD(day, -30, CURRENT_TIMESTAMP())
GROUP BY b.NAME
ORDER BY RETIRED_DEALS DESC;