About Temp Tables

In some cases, a relatively simple set of SQL logic can remove a large percentage of situations that have no chance of satisfying the criteria for a particular scenario. Under these conditions, it is often an efficient strategy to perform an initial screening step that identifies candidate entities and stores them in a temporary table. The other datasets can then use the temp table as a starting point, thereby limiting the volume of data that they need to retrieve.

For example, an earlier release of the Brokerage and Investor Protection product included a scenario to identify mutual fund switches, which are cases in which a customer performs short-term trading in a mutual fund. Specifically, the customer must:

  1. Buy a mutual fund.
  2. Sell the same mutual fund after less than six months after event (1).
  3. Buy a different mutual fund sells within seven days of event (2).

The situation is of interest if a representative solicits event (2) or event (3). The scenario is run daily. The challenge in developing this scenario is that large brokerage firms may have more than two million mutual fund trades per month. Consequently, a scenario takes a long time to run if it has to process all mutual fund trades for a six-month period. To shorten the run time, the scenario uses a temp table that identifies the distinct list of accounts that met three conditions:

  1. The account performed a mutual fund purchase on the most recent day.
  2. The account had performed a mutual fund sale in last seven days.
  3. A representative solicited at least one of the account’s mutual fund trades during this period.

The SQL script for the temp table ran within a few minutes and the output identified a limited number of accounts. The system uses this temp table in the final pattern dataset to retrieve the entire six months of data only for the accounts in the temp table. Using this approach, the system retrieves a manageable amount of data and the detection process runs in an acceptable amount of time.