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: campaign
|   campaign_id | campaign_type   | start_date              | end_date                |   Intervel |
|--------------:|:----------------|:------------------------|:------------------------|-----------:|
|            24 | Y               | 2013-10-21 00:00:00.000 | 2013-12-20 00:00:00.000 |         60 |
|            25 | Y               | 2013-10-21 00:00:00.000 | 2013-11-22 00:00:00.000 |         32 |
|            20 | Y               | 2013-09-07 00:00:00.000 | 2013-11-16 00:00:00.000 |         70 |
|            23 | Y               | 2013-10-08 00:00:00.000 | 2013-11-15 00:00:00.000 |         38 |
|            21 | Y               | 2013-09-16 00:00:00.000 | 2013-10-18 00:00:00.000 |         32 |
|            22 | X               | 2013-09-16 00:00:00.000 | 2013-10-18 00:00:00.000 |         32 |
|            18 | X               | 2013-08-10 00:00:00.000 | 2013-10-04 00:00:00.000 |         55 |
|            19 | Y               | 2013-08-26 00:00:00.000 | 2013-09-27 00:00:00.000 |         32 |
|            17 | Y               | 2013-07-29 00:00:00.000 | 2013-08-30 00:00:00.000 |         32 |
|            16 | Y               | 2013-07-15 00:00:00.000 | 2013-08-16 00:00:00.000 |         32 |


Table: coupon
|   coupon_id |   item_id |
|------------:|----------:|
|          23 |     16915 |
|          23 |     24122 |
|          23 |     24142 |
|          23 |     58675 |
|          23 |     58695 |
|          23 |      9212 |
|          23 |      9228 |
|          23 |     28034 |
|          23 |      5804 |
|          23 |      9300 |


Table: demographic
|   customer_id | age_bracket   | Marriage_status   | rent   | family_size   | children    | income_catagory   |
|--------------:|:--------------|:------------------|:-------|:--------------|:------------|:------------------|
|             1 | most senior   | Married           | no     | small         | no children | low income        |
|             6 | middle aged   | Married           | no     | small         | no children | medium income     |
|            12 | middle aged   | Married           | no     | small         | no children | medium income     |
|            14 | adult         | Married           | yes    | small         | no children | medium income     |
|            15 | middle aged   | Married           | no     | small         | no children | medium income     |
|            27 | aged          | Married           | no     | small         | no children | medium income     |
|            36 | aged          | Married           | no     | small         | no children | low income        |
|            39 | most senior   | Married           | no     | small         | no children | low income        |
|            48 | aged          | Married           | no     | small         | no children | low income        |
|            51 | most senior   | Married           | no     | small         | no children | low income        |


Table: item
|   item_id |   brand | brand_type   | category   |
|----------:|--------:|:-------------|:-----------|
|         4 |      56 | Local        | Grocery    |
|         5 |      56 | Local        | Grocery    |
|         6 |      56 | Local        | Grocery    |
|        10 |      56 | Local        | Grocery    |
|        12 |      56 | Local        | Grocery    |
|        14 |      56 | Local        | Grocery    |
|        15 |      56 | Local        | Grocery    |
|        16 |      56 | Local        | Grocery    |
|        17 |      56 | Local        | Grocery    |
|        18 |      56 | Local        | Grocery    |


Table: train
|    id |   campaign_id |   coupon_id |   customer_id | Redemption_status   |
|------:|--------------:|------------:|--------------:|:--------------------|
|   662 |            13 |         386 |           567 | NO REDEM            |
|  4203 |            13 |         386 |           611 | NO REDEM            |
|  5925 |            13 |         386 |           390 | NO REDEM            |
|  7375 |            13 |         386 |           636 | NO REDEM            |
|  9458 |            13 |         386 |           571 | NO REDEM            |
| 11991 |            13 |         386 |            23 | NO REDEM            |
| 12259 |            13 |         386 |           868 | NO REDEM            |
| 13791 |            13 |         386 |           349 | NO REDEM            |
| 14666 |            13 |         386 |           391 | NO REDEM            |
| 16795 |            13 |         386 |           894 | NO REDEM            |


Table: transaction
| date                    |   customer_id |   item_id |   quantity |   selling_price |   other_discount |   coupon_discount |   Price without discount |   percentage discount |
|:------------------------|--------------:|----------:|-----------:|----------------:|-----------------:|------------------:|-------------------------:|----------------------:|
| 2012-01-03 00:00:00.000 |           171 |     17047 |          1 |           106.5 |                0 |                 0 |                    106.5 |                     0 |
| 2012-02-06 00:00:00.000 |           240 |     17047 |          1 |           106.5 |                0 |                 0 |                    106.5 |                     0 |
| 2012-02-09 00:00:00.000 |           714 |     17047 |          1 |           106.5 |                0 |                 0 |                    106.5 |                     0 |
| 2012-02-12 00:00:00.000 |          1332 |     17047 |          1 |           106.5 |                0 |                 0 |                    106.5 |                     0 |
| 2012-02-15 00:00:00.000 |           989 |     17047 |          1 |           106.5 |                0 |                 0 |                    106.5 |                     0 |
| 2012-02-22 00:00:00.000 |           741 |     17047 |          1 |           106.5 |                0 |                 0 |                    106.5 |                     0 |
| 2012-02-26 00:00:00.000 |           989 |     17047 |          1 |           106.5 |                0 |                 0 |                    106.5 |                     0 |
| 2012-02-26 00:00:00.000 |          1332 |     17047 |          1 |           106.5 |                0 |                 0 |                    106.5 |                     0 |
| 2012-03-06 00:00:00.000 |           599 |     17047 |          1 |           106.5 |                0 |                 0 |                    106.5 |                     0 |
| 2012-03-07 00:00:00.000 |          1243 |     17047 |          1 |           106.5 |                0 |                 0 |                    106.5 |                     0 |

 


tables: name: campaign
data: campaign.csv
name: coupon
data: coupon.csv
name: demographic
data: demographic.csv
name: item
data: item.csv
name: train
data: train.csv
name: transaction
data: transaction.csv
label: train
campaign_id
campaign
campaign_id
coupon
item_id
item
item_id
transaction
item_id
item
item_id
train
customer_id
demographic
customer_id
transaction
customer_id
demographic
customer_id
output: {
    "joins": [
        {
            "from_table": "train",
            "from_column": "campaign_id",
            "to_table": "campaign",
            "to_column": "campaign_id"
        },
        {
            "from_table": "coupon",
            "from_column": "item_id",
            "to_table": "item",
            "to_column": "item_id"
        },
        {
            "from_table": "transaction",
            "from_column": "item_id",
            "to_table": "item",
            "to_column": "item_id"
        },
        {
            "from_table": "train",
            "from_column": "customer_id",
            "to_table": "demographic",
            "to_column": "customer_id"
        },
        {
            "from_table": "transaction",
            "from_column": "customer_id",
            "to_table": "demographic",
            "to_column": "customer_id"
        }
    ]
}