'Transforming data in a special format

First, I'll show how data looks, and then to what I'm trying it to transform.

data.head()

+------------+------+------+-------+---------+
|   Date     | col1 | col2 | col3  | Target  |
+------------+------+------+-------+---------+
| 2020-01-01 | a    | c    | f     |       1 |
| 2020-01-01 | b    | c    | g     |       0 |
| 2020-01-01 | b    | d    | g     |       1 |
| 2020-01-02 | a    | c    | g     |       1 |
| 2020-01-02 | a    | d    | f     |       0 |
| 2020-01-02 | b    | e    | g     |       0 |
| 2020-01-02 | a    | d    | g     |       1 |
| 2020-01-03 | b    | d    | f     |       1 |
| 2020-01-03 | b    | c    | g     |       1 |
| 2020-01-03 | b    | c    | g     |       0 |
+------------+------+------+-------+---------+

I'm trying to transform data in such a manner, that I want to group data by dates, where

Volume Percentage: sum of each of the feature categories corresponding to each date.

Target percentage: sum of target values corresponding to each of the feature categories at a specific date.

NOTE: The example shown below is for a specific date, but I want to do the same thing at the each date level.

transformed_data.head():

+------------+------------------+--------------+---------------+--------------+
|   Date     | feature_category | feature_name | volumne_perct | target_perct |
+------------+------------------+--------------+---------------+--------------+
| 2020-01-01 | a                | col1         |             1 |            1 |
| 2020-01-01 | b                | col1         |             2 |            2 |
| 2020-01-01 | c                | col2         |             2 |            1 |
| 2020-01-01 | d                | col2         |             1 |            1 |
| 2020-01-01 | e                | col2         |             0 |            0 |
| 2020-01-01 | f                | col3         |             1 |            1 |
| 2020-01-01 | g                | col3         |             2 |            1 |
+------------+------------------+--------------+---------------+--------------+

I've written code for this in python, but that's not very efficient, and is computationally very expensive, also very lengthy code.

Hence, this time, I want to do this with SQL( and I'm not proficient in it), can you please guide me on the logic for the same, It'll be extremely helpful for me, cause I'm stuck at this part from a really long time.

Thanks



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source