Task: equi-join-detect (source dataset: Auto-BI)

Your are given a database with related tables used for business intelligence analysis. Your task is to identify all key/foreign-key join relationships between the tables for this database. Recall that a join is a relationship between two tables, that allows rows from one table to be combined with rows from another, based on a set of common join columns.
 
 
No explanation needed. Please return all join relationships in the following JSON format:
{
  "joins": [
    {
      "from_table": "table-1",
      "from_column": "column-1",
      "to_table": "table-2",
      "to_column": "column-3"
    }, 
	...
  ]
}

Tables:

Table: Company Expenses
| Expenses Category    | Date                    |   Expense Value |
|:---------------------|:------------------------|----------------:|
| Professional Service | 2019-09-01 00:00:00.000 |           25.54 |
| Other Expenses       | 2019-09-01 00:00:00.000 |            0.9  |
| Advertising Expense  | 2019-09-14 00:00:00.000 |           40.36 |
| Travel Expense       | 2019-09-14 00:00:00.000 |            9.69 |
| COS - Other Costs    | 2019-09-15 00:00:00.000 |           11.95 |
| Advertising Expense  | 2019-09-15 00:00:00.000 |           23.82 |
| Advertising Expense  | 2019-09-15 00:00:00.000 |          139.6  |
| Office Supplies      | 2019-09-25 00:00:00.000 |           34.95 |
| Travel Expense       | 2019-09-25 00:00:00.000 |           34.95 |
| Professional Service | 2019-09-28 00:00:00.000 |           50    |


Table: Dates
| Date                    |   Year |   QuarterOfYear |   MonthOfYear |   DayOfMonth |   DateInt | Month Name   | Month & Year   | Quarter & Year   |   DayOfWeek | DayOfWeekName   | WeekEnding              | MonthEnding             |   ISO Weeknumber |   ISO Year |   ISO Day of Year | Week & Year   |   WeeknYear |   MonthnYear |   QuarternYear | Fiscal Year   | Fiscal Quarter   |   Fiscal Period | IsAfterToday   | IsWorkingDay   | IsHoliday   |   WeekOffset |   MonthOffset |   QuarterOffset |   YearOffset |   FiscalYearOffset | WeekCompleted   | MonthCompleted   | QuarterCompleted   | Short Month Name   |
|:------------------------|-------:|----------------:|--------------:|-------------:|----------:|:-------------|:---------------|:-----------------|------------:|:----------------|:------------------------|:------------------------|-----------------:|-----------:|------------------:|:--------------|------------:|-------------:|---------------:|:--------------|:-----------------|----------------:|:---------------|:---------------|:------------|-------------:|--------------:|----------------:|-------------:|-------------------:|:----------------|:-----------------|:-------------------|:-------------------|
| 2019-12-30 00:00:00.000 |   2019 |               4 |            12 |           30 |  20191230 | December     | Dec 2019       | Q4 2019          |           1 | Monday          | 2020-01-05 00:00:00.000 | 2019-12-31 00:00:00.000 |                1 |       2020 |                 1 | 2020-01       |    20200100 |     20191200 |       20190400 | FY19          | FQ4              |              12 | False          | True           | Unknown     |          -28 |            -7 |              -3 |           -1 |                 -1 | True            | True             | True               | Dec                |
| 2019-12-31 00:00:00.000 |   2019 |               4 |            12 |           31 |  20191231 | December     | Dec 2019       | Q4 2019          |           2 | Tuesday         | 2020-01-05 00:00:00.000 | 2019-12-31 00:00:00.000 |                1 |       2020 |                 2 | 2020-01       |    20200100 |     20191200 |       20190400 | FY19          | FQ4              |              12 | False          | True           | Unknown     |          -28 |            -7 |              -3 |           -1 |                 -1 | True            | True             | True               | Dec                |
| 2020-01-01 00:00:00.000 |   2020 |               1 |             1 |            1 |  20200101 | January      | Jan 2020       | Q1 2020          |           3 | Wednesday       | 2020-01-05 00:00:00.000 | 2020-01-31 00:00:00.000 |                1 |       2020 |                 3 | 2020-01       |    20200100 |     20200100 |       20200100 | FY20          | FQ1              |               1 | False          | True           | Unknown     |          -28 |            -6 |              -2 |            0 |                  0 | True            | True             | True               | Jan                |
| 2020-01-02 00:00:00.000 |   2020 |               1 |             1 |            2 |  20200102 | January      | Jan 2020       | Q1 2020          |           4 | Thursday        | 2020-01-05 00:00:00.000 | 2020-01-31 00:00:00.000 |                1 |       2020 |                 4 | 2020-01       |    20200100 |     20200100 |       20200100 | FY20          | FQ1              |               1 | False          | True           | Unknown     |          -28 |            -6 |              -2 |            0 |                  0 | True            | True             | True               | Jan                |
| 2020-01-05 00:00:00.000 |   2020 |               1 |             1 |            5 |  20200105 | January      | Jan 2020       | Q1 2020          |           0 | Sunday          | 2020-01-05 00:00:00.000 | 2020-01-31 00:00:00.000 |                1 |       2020 |                 7 | 2020-01       |    20200100 |     20200100 |       20200100 | FY20          | FQ1              |               1 | False          | True           | Unknown     |          -28 |            -6 |              -2 |            0 |                  0 | True            | True             | True               | Jan                |
| 2020-01-06 00:00:00.000 |   2020 |               1 |             1 |            6 |  20200106 | January      | Jan 2020       | Q1 2020          |           1 | Monday          | 2020-01-12 00:00:00.000 | 2020-01-31 00:00:00.000 |                2 |       2020 |                 8 | 2020-02       |    20200200 |     20200100 |       20200100 | FY20          | FQ1              |               1 | False          | True           | Unknown     |          -27 |            -6 |              -2 |            0 |                  0 | True            | True             | True               | Jan                |
| 2020-01-07 00:00:00.000 |   2020 |               1 |             1 |            7 |  20200107 | January      | Jan 2020       | Q1 2020          |           2 | Tuesday         | 2020-01-12 00:00:00.000 | 2020-01-31 00:00:00.000 |                2 |       2020 |                 9 | 2020-02       |    20200200 |     20200100 |       20200100 | FY20          | FQ1              |               1 | False          | True           | Unknown     |          -27 |            -6 |              -2 |            0 |                  0 | True            | True             | True               | Jan                |
| 2020-01-08 00:00:00.000 |   2020 |               1 |             1 |            8 |  20200108 | January      | Jan 2020       | Q1 2020          |           3 | Wednesday       | 2020-01-12 00:00:00.000 | 2020-01-31 00:00:00.000 |                2 |       2020 |                10 | 2020-02       |    20200200 |     20200100 |       20200100 | FY20          | FQ1              |               1 | False          | True           | Unknown     |          -27 |            -6 |              -2 |            0 |                  0 | True            | True             | True               | Jan                |
| 2020-01-09 00:00:00.000 |   2020 |               1 |             1 |            9 |  20200109 | January      | Jan 2020       | Q1 2020          |           4 | Thursday        | 2020-01-12 00:00:00.000 | 2020-01-31 00:00:00.000 |                2 |       2020 |                11 | 2020-02       |    20200200 |     20200100 |       20200100 | FY20          | FQ1              |               1 | False          | True           | Unknown     |          -27 |            -6 |              -2 |            0 |                  0 | True            | True             | True               | Jan                |
| 2020-01-12 00:00:00.000 |   2020 |               1 |             1 |           12 |  20200112 | January      | Jan 2020       | Q1 2020          |           0 | Sunday          | 2020-01-12 00:00:00.000 | 2020-01-31 00:00:00.000 |                2 |       2020 |                14 | 2020-02       |    20200200 |     20200100 |       20200100 | FY20          | FQ1              |               1 | False          | True           | Unknown     |          -27 |            -6 |              -2 |            0 |                  0 | True            | True             | True               | Jan                |


Table: Expense DB
| Expense Category        |   Expense Category ID |
|:------------------------|----------------------:|
| Advertising Expense     |                     1 |
| Advertising fee         |                     2 |
| Business Documents      |                     3 |
| COS - Comission Fee     |                     4 |
| COS - Purchase Material |                     5 |
| COS - Other Costs       |                     6 |
| Delivery / Postage      |                     7 |
| Education/Training      |                     8 |
| Office Supplies         |                     9 |
| Other Expenses          |                    10 |


Table: Expenses
| Date                    |   Expense Category ID |   Expense Value | Comments   | Expenses Category    |
|:------------------------|----------------------:|----------------:|:-----------|:---------------------|
| 2019-09-01 00:00:00.000 |                    12 |           25.54 |            | Professional Service |
| 2019-09-01 00:00:00.000 |                    10 |            0.9  |            | Other Expenses       |
| 2019-09-14 00:00:00.000 |                     1 |           40.36 |            | Advertising Expense  |
| 2019-09-14 00:00:00.000 |                    15 |            9.69 |            | Travel Expense       |
| 2019-09-15 00:00:00.000 |                     6 |           11.95 |            | COS - Other Costs    |
| 2019-09-15 00:00:00.000 |                     1 |           23.82 |            | Advertising Expense  |
| 2019-09-15 00:00:00.000 |                     1 |          139.6  |            | Advertising Expense  |
| 2019-09-25 00:00:00.000 |                     9 |           34.95 |            | Office Supplies      |
| 2019-09-25 00:00:00.000 |                    15 |           34.95 |            | Travel Expense       |
| 2019-09-28 00:00:00.000 |                    12 |           50    |            | Professional Service |


Table: Income Analysis



Table: Income Statement Data
| Category             | Date                    |   Values | Type   |   Type Index |
|:---------------------|:------------------------|---------:|:-------|-------------:|
| Professional Service | 2019-09-01 00:00:00.000 |    25.54 | COGS   |            2 |
| Other Expenses       | 2019-09-01 00:00:00.000 |     0.9  | COGS   |            2 |
| Advertising Expense  | 2019-09-14 00:00:00.000 |    40.36 | COGS   |            2 |
| Travel Expense       | 2019-09-14 00:00:00.000 |     9.69 | COGS   |            2 |
| COS - Other Costs    | 2019-09-15 00:00:00.000 |    11.95 | COGS   |            2 |
| Advertising Expense  | 2019-09-15 00:00:00.000 |    23.82 | COGS   |            2 |
| Advertising Expense  | 2019-09-15 00:00:00.000 |   139.6  | COGS   |            2 |
| Office Supplies      | 2019-09-25 00:00:00.000 |    34.95 | COGS   |            2 |
| Travel Expense       | 2019-09-25 00:00:00.000 |    34.95 | COGS   |            2 |
| Professional Service | 2019-09-28 00:00:00.000 |    50    | COGS   |            2 |


Table: Income Statement Template
|   Row Index | Income Statement Items    | Summary   | Items (Normalized)        | Summary Index   |
|------------:|:--------------------------|:----------|:--------------------------|:----------------|
|           1 | Distribution              |           | Distribution              |                 |
|           2 | Export                    |           | Export                    |                 |
|           3 | Wholesale                 |           | Wholesale                 |                 |
|           4 | Online                    |           | Online                    |                 |
|           5 | Revenues                  | Revenues  | Revenues                  | 1               |
|           6 |                           |           |                           |                 |
|           7 | COS - Beginning Inventory |           | COS - Beginning Inventory |                 |
|           8 | COS - Purchase Material   |           | COS - Purchase Material   |                 |
|           9 | COS - Logistic Cost       |           | COS - Logistic Cost       |                 |
|          10 | COS - Comission Fee       |           | COS - Comission Fee       |                 |


Table: Inventory Data
| Month & Year   | First Date              | Last Date               | Beginning Inventory Units   |   Ending Inventory Units | Beginning Inventory €   |   Ending Inventory € |
|:---------------|:------------------------|:------------------------|:----------------------------|-------------------------:|:------------------------|---------------------:|
| Sep 2019       | 2019-09-01 00:00:00.000 | 2019-09-30 00:00:00.000 |                             |                      123 |                         |               366.65 |
| Oct 2019       | 2019-10-01 00:00:00.000 | 2019-10-31 00:00:00.000 | 123                         |                      382 | 366.65                  |               827.62 |
| Nov 2019       | 2019-11-01 00:00:00.000 | 2019-11-30 00:00:00.000 | 382                         |                      189 | 827.62                  |               202.69 |
| Dec 2019       | 2019-12-01 00:00:00.000 | 2019-12-31 00:00:00.000 | 189                         |                      742 | 202.69                  |               972.7  |
| Jan 2020       | 2020-01-01 00:00:00.000 | 2020-01-31 00:00:00.000 | 742                         |                      464 | 972.7                   |               428.77 |
| Feb 2020       | 2020-02-01 00:00:00.000 | 2020-02-29 00:00:00.000 | 464                         |                     1856 | 428.77                  |              2925.53 |
| Mar 2020       | 2020-03-01 00:00:00.000 | 2020-03-31 00:00:00.000 | 1856                        |                     1488 | 2925.53                 |              2569.15 |
| Apr 2020       | 2020-04-01 00:00:00.000 | 2020-04-30 00:00:00.000 | 1488                        |                     1130 | 2569.15                 |              2193.05 |
| May 2020       | 2020-05-01 00:00:00.000 | 2020-05-31 00:00:00.000 | 1130                        |                      404 | 2193.05                 |               958.38 |
| Jun 2020       | 2020-06-01 00:00:00.000 | 2020-06-30 00:00:00.000 | 404                         |                     2273 | 958.38                  |              4470.13 |


Table: Metric Selection
| Metric Name              |   Metric ID |
|:-------------------------|------------:|
| Revenue                  |           1 |
| Cost                     |           2 |
| Net Income & Expenditure |           3 |
| Profit                   |           4 |


Table: Purchase
| Date                    |   Shipping ID |   Subproduct ID |   Number of Boxes | Category                |   Purchase Value |
|:------------------------|--------------:|----------------:|------------------:|:------------------------|-----------------:|
| 2019-09-01 00:00:00.000 |             1 |              91 |                 2 | COS - Purchase Material |            24    |
| 2019-09-01 00:00:00.000 |             2 |              42 |                 4 | COS - Purchase Material |           127.68 |
| 2019-09-01 00:00:00.000 |             2 |              52 |                 3 | COS - Purchase Material |           179.64 |
| 2019-09-01 00:00:00.000 |             2 |              32 |                 4 | COS - Purchase Material |           157.52 |
| 2019-09-26 00:00:00.000 |             3 |              43 |                 8 | COS - Purchase Material |           255.36 |
| 2019-09-26 00:00:00.000 |             3 |              53 |                 7 | COS - Purchase Material |           419.16 |
| 2019-09-26 00:00:00.000 |             3 |              33 |                 4 | COS - Purchase Material |           157.52 |
| 2019-10-10 00:00:00.000 |             4 |              44 |                20 | COS - Purchase Material |           638.4  |
| 2019-10-10 00:00:00.000 |             4 |              74 |                 4 | COS - Purchase Material |           199.6  |
| 2019-10-10 00:00:00.000 |             4 |              84 |                 4 | COS - Purchase Material |           140    |


Table: Sales
| Date                    |   Subproduct ID |   Client ID |   Units Sold |   Sale Price | Type of Payment   | Payment Status   |   Status ID | Order ID       | Category   |
|:------------------------|----------------:|------------:|-------------:|-------------:|:------------------|:-----------------|------------:|:---------------|:-----------|
| 2020-03-09 00:00:00.000 |              46 |           6 |            1 |         4.42 | Trans Bancaria    | Pagado           |           2 | 101            | Wholesale  |
| 2020-03-31 00:00:00.000 |              46 |           6 |            1 |         4.62 | Paypal            | Pagado           |           2 | WEB2020-000001 | Online     |
| 2020-04-01 00:00:00.000 |              46 |           6 |            1 |         4.91 | Paypal            | Pagado           |           2 | WEB2020-000002 | Online     |
| 2020-04-06 00:00:00.000 |              46 |           6 |            1 |         4.91 | Stripe            | Pagado           |           2 | WEB2020-000004 | Online     |
| 2020-04-06 00:00:00.000 |              46 |           6 |            1 |         4.91 | Stripe            | Pagado           |           2 | WEB2020-000005 | Online     |
| 2020-04-07 00:00:00.000 |              46 |           6 |            1 |         4.62 | Stripe            | Pagado           |           2 | WEB2020-000006 | Online     |
| 2020-04-08 00:00:00.000 |              46 |           6 |            1 |         4.62 | Stripe            | Pagado           |           2 | WEB2020-000007 | Online     |
| 2020-04-08 00:00:00.000 |              46 |           6 |            1 |         4.91 | Stripe            | Pagado           |           2 | WEB2020-000007 | Online     |
| 2020-04-09 00:00:00.000 |              46 |           6 |            1 |         4.62 | Stripe            | Pagado           |           2 | WEB2020-000008 | Online     |
| 2020-04-12 00:00:00.000 |              46 |           6 |            1 |         4.91 | Stripe            | Pagado           |           2 | WEB2020-000009 | Online     |


Table: Shipping
|   Shipping ID | Date                    |   Nett Shipping Weight |   Expenses ID |   Expense Value | Expense Category    |
|--------------:|:------------------------|-----------------------:|--------------:|----------------:|:--------------------|
|             1 | 2019-09-01 00:00:00.000 |                  16.8  |             5 |            0    | COS - Logistic Cost |
|             2 | 2019-09-01 00:00:00.000 |                  38.96 |             5 |           60    | COS - Logistic Cost |
|             3 | 2019-09-26 00:00:00.000 |                  75.12 |             5 |           80    | COS - Logistic Cost |
|             4 | 2019-10-10 00:00:00.000 |                  88    |             5 |           60    | COS - Logistic Cost |
|             5 | 2019-12-04 00:00:00.000 |                 149.6  |             5 |          134.6  | COS - Logistic Cost |
|             6 | 2020-02-01 00:00:00.000 |                 409.86 |             5 |          238.1  | COS - Logistic Cost |
|             7 | 2020-02-11 00:00:00.000 |                  83.9  |             5 |           98.11 | COS - Logistic Cost |
|             8 | 2020-02-15 00:00:00.000 |                  28.8  |             5 |            0    | COS - Logistic Cost |
|             9 | 2020-06-01 00:00:00.000 |                1213.28 |             5 |          770.55 | COS - Logistic Cost |
|            10 | 2020-06-09 00:00:00.000 |                  28.8  |             5 |            0    | COS - Logistic Cost |


Table: Subproducts
|   Product ID |   Shipping ID |   Subproduct ID |   Supplier Price |   Nett Weight |   Nett + Tare Weight |   Units per Box |   Product Weight | Expiration Date         |   Shipping Cost per Product |   Purchase Cost per Product |
|-------------:|--------------:|----------------:|-----------------:|--------------:|---------------------:|----------------:|-----------------:|:------------------------|----------------------------:|----------------------------:|
|            9 |             1 |              91 |             0.5  |         0.35  |                0.42  |              24 |           10.18  | 2020-09-30 00:00:00.000 |                      0      |                      0.5    |
|            4 |             2 |              42 |             3.99 |         0.38  |                0.582 |               8 |            4.768 | 2020-09-30 00:00:00.000 |                      0.5852 |                      4.5752 |
|            5 |             2 |              52 |             4.99 |         0.5   |                0.568 |              12 |            6.966 | 2021-03-31 00:00:00.000 |                      0.77   |                      5.76   |
|            3 |             2 |              32 |             1.79 |         0.1   |                0.11  |              22 |            2.485 | 2020-09-26 00:00:00.000 |                      0.154  |                      1.944  |
|            4 |             3 |              43 |             3.99 |         0.38  |                0.582 |               8 |            4.768 | 2020-09-30 00:00:00.000 |                      0.4047 |                      4.3947 |
|            5 |             3 |              53 |             4.99 |         0.5   |                0.568 |              12 |            6.966 | 2021-03-31 00:00:00.000 |                      0.5325 |                      5.5225 |
|            3 |             3 |              33 |             1.79 |         0.1   |                0.11  |              22 |            2.485 | 2020-09-26 00:00:00.000 |                      0.1065 |                      1.8965 |
|            4 |             4 |              44 |             3.99 |         0.38  |                0.582 |               8 |            4.768 | 2020-09-30 00:00:00.000 |                      0.2591 |                      4.2491 |
|            7 |             4 |              74 |             4.99 |         0.5   |                0.512 |              10 |            5.44  | 2021-03-31 00:00:00.000 |                      0.3409 |                      5.3309 |
|            8 |             4 |              84 |             0.35 |         0.018 |                0.02  |             100 |            2.13  | 2021-03-31 00:00:00.000 |                      0.0123 |                      0.3623 |

 


tables: name: Company Expenses
data: Company Expenses.csv
name: Dates
data: Dates.csv
name: Expense DB
data: Expense DB.csv
name: Expenses
data: Expenses.csv
name: Income Analysis
data: Income Analysis.csv
name: Income Statement Data
data: Income Statement Data.csv
name: Income Statement Template
data: Income Statement Template.csv
name: Inventory Data
data: Inventory Data.csv
name: Metric Selection
data: Metric Selection.csv
name: Purchase
data: Purchase.csv
name: Sales
data: Sales.csv
name: Shipping
data: Shipping.csv
name: Subproducts
data: Subproducts.csv
label: Purchase
Subproduct ID
Subproducts
Subproduct ID
Subproducts
Shipping ID
Shipping
Shipping ID
Sales
Date
Dates
Date
Sales
Subproduct ID
Subproducts
Subproduct ID
Expenses
Expense Category ID
Expense DB
Expense Category ID
Expenses
Date
Dates
Date
Shipping
Expenses ID
Expense DB
Expense Category ID
Purchase
Date
Dates
Date
output: {
    "joins": [
        {
            "from_table": "Purchase",
            "from_column": "Subproduct ID",
            "to_table": "Subproducts",
            "to_column": "Subproduct ID"
        },
        {
            "from_table": "Subproducts",
            "from_column": "Shipping ID",
            "to_table": "Shipping",
            "to_column": "Shipping ID"
        },
        {
            "from_table": "Sales",
            "from_column": "Date",
            "to_table": "Dates",
            "to_column": "Date"
        },
        {
            "from_table": "Sales",
            "from_column": "Subproduct ID",
            "to_table": "Subproducts",
            "to_column": "Subproduct ID"
        },
        {
            "from_table": "Expenses",
            "from_column": "Expense Category ID",
            "to_table": "Expense DB",
            "to_column": "Expense Category ID"
        },
        {
            "from_table": "Expenses",
            "from_column": "Date",
            "to_table": "Dates",
            "to_column": "Date"
        },
        {
            "from_table": "Shipping",
            "from_column": "Expenses ID",
            "to_table": "Expense DB",
            "to_column": "Expense Category ID"
        },
        {
            "from_table": "Purchase",
            "from_column": "Date",
            "to_table": "Dates",
            "to_column": "Date"
        }
    ]
}