How to compare two tables, in order to calculate the growth rate between two time periods?

Question

The table I currently have looks like this: (data comes from two different tables, 19921231, 19930331)

The table I want to create looks like this (5th column added)

Goal: determine the deposit growth rate at each bank. I.e. Comparing the amount of deposits held at a bank in the previous quarter (e.g. 19921231) to the deposits of the most recent quarter (e.g. 19930331). Then view the increase/decrease as a percentage.

This is the code I have written so far:

select 
AL.repdte as `Date`, AL.cert, AL.name, AL.dep as `Deposits`
FROM usa_fdic_call_reports_1992.All_Reports_19921231_Assets_and_Liabilities as AL

UNION ALL

select 
AL.repdte as `Date`, AL.cert, AL.name, AL.dep as `Deposits`
FROM usa_fdic_call_reports_1993.All_Reports_19930331_Assets_and_Liabilities as AL

An answer to this question suggested this code, that works However, for some reason I getting an output of "NULL"

select al19930331.repdte as `Date`, al19930331.cert, al19930331.name,
       al19930331.dep as Deposits_1993,
       al19921231.dep as Deposits_1992,
       (al19930331.dep - al19921231.dep) / al19921231.dep as grow_rate
from usa_fdic_call_reports_1993.All_Reports_19930331_Assets_and_Liabilities as al19930331 left join
     usa_fdic_call_reports_1992.All_Reports_19921231_Assets_and_Liabilities as al19921231
     on al19930331.cert = al19921231.cert and
        al19930331.name = al19921231.name and
        al19921231.repdte = date_add(al19930331.repdte, interval 1 year);

In an attempt to isolate the "NULL" issue, I reduced the query to it's simplest terms, and was able to eliminate the "NULL" issue.

Now we have deposit columns for both quarters returning what appears to be proper outputs.

Next I removed the last line of code from:

select al19930331.repdte as `Date`, al19930331.cert, al19930331.name,
       al19930331.dep as Deposits_1993,
       al19921231.dep as Deposits_1992,
       (al19930331.dep - al19921231.dep) / al19921231.dep as grow_rate
from usa_fdic_call_reports_1993.All_Reports_19930331_Assets_and_Liabilities as al19930331 left join
     usa_fdic_call_reports_1992.All_Reports_19921231_Assets_and_Liabilities as al19921231
     on al19930331.cert = al19921231.cert and
        al19930331.name = al19921231.name and
        al19921231.repdte = date_add(al19930331.repdte, interval 1 year);

Removing the last line of code worked, sort of. Running the code produces a "division by zero" error. How can one eliminate the division by zero error?

select al19930331.repdte as `Date`, al19930331.cert, al19930331.name,
       al19930331.dep as Deposits_1993,
       al19921231.dep as Deposits_1992,
       (al19930331.dep - al19921231.dep) / al19921231.dep as grow_rate
from usa_fdic_call_reports_1993.All_Reports_19930331_Assets_and_Liabilities as al19930331 left join
     usa_fdic_call_reports_1992.All_Reports_19921231_Assets_and_Liabilities as al19921231
     on al19930331.cert = al19921231.cert and
        al19930331.name = al19921231.name

Answer 1

You should not be storing this information in different tables. This should all be in the same table using different partitions. But with the date embedded in the middle of the name, I think you need to use an explicit join:

    select al19930331.repdte as `Date`, al19930331.cert, al19930331.name,
       al19930331.dep as Deposits_1993_0331,
       al19921231.dep as Deposits_1992_1231,
       (al19930331.dep - al19921231.dep) / al19921231.dep as grow_rate
from usa_fdic_call_reports_1993.All_Reports_19930331_Assets_and_Liabilities as al19930331 left join
     usa_fdic_call_reports_1992.All_Reports_19921231_Assets_and_Liabilities as al19921231
     on al19930331.cert = al19921231.cert and 
     al19921231.repdte = date_add(al19930331.repdte, interval 1 quarter);

This would be simpler with the data in one table.