Question: Purchases table

```
create table purchase ( 
id integer primary key, 
purchasedate date not null, 
purchasesum numeric(12,2) not null )

```

contains purchase sums. How to find change percent from previos year:

```
select
  extract( year from purchasedate ),
  sum( purchasesum),
  ( sum( purchasesum) - previous row sum( previous purchasesum ) ) /
                previous row sum( previous purchasesum ) * 100
from purchase
group by 1
order by 1

```

Result should be

```
2014     1200
2015     1320    +10%
2016     14784   +12%
2017     1774.08 +20%
2018     1596.67 -10% 
2019     2075.67 +30%

```

Using Postgresql 9.1+

Answer: 
This is how I have managed to install dependencies from private GitHub repositories.

Dependencies in package.json can be added as follows. The `github:` prefix is optional. Specifying the `#branch` or `#tag` is also optional.

```
    "dependencies": {
        ...
        "myrepo": "username/myrepo#master",
        "myotherrepo": "github:username/myotherrepo"
    },

```

Here is an example workflow. `PAT` is a `repo` scoped [Personal Access Token](https://help.github.com/en/github/authenticating-to-github/creating-a-personal-access-token-for-the-command-line). It is important to disable persisted credentials on `actions/checkout`, otherwise they will override your `PAT`. Note that the `git config` change persists between steps so you only need to run it once per job.

```
      - uses: actions/checkout@v2
        with:
          persist-credentials: false
      - uses: actions/setup-node@v1
        with:
          node-version: 12.x
      - run: git config --global url."https://${{ secrets.PAT }}@github.com/".insteadOf ssh://git@github.com/
      - run: npm ci
      ...

```
A very straightforward solution that requires only **read-only** access to the repo. Uses GitHub's Deploy Keys and the following action <https://github.com/webfactory/ssh-agent>

#### 1. Provide a private repo in `package.json` in the following format:

```
"dependencies": {
    "repo": "git+ssh://git@github.com:user/private_repo.git",
}

```

#### 2. Generate a deploy key locally on your machine. Use empty passphrase

```
ssh-keygen -f my_key

```

#### 3. Create a deploy key via GitHub UI (prefer read-only). Use contents of `my_key.pub`

[![Deploy keys](https://i.stack.imgur.com/8oR5r.png)](https://i.stack.imgur.com/8oR5r.png)

#### 4. Create a GitHub Actions secret named `REPO_SSH_KEY` for the target repo via GitHub UI - the one that will be running GitHub Actions. Use contents of `my_key` – it is a private key

[![SSH secret](https://i.stack.imgur.com/U9Oh2.png)](https://i.stack.imgur.com/U9Oh2.png)

#### 5. Update the GitHub Actions workflow file to look like this

```
# ...
jobs:
  build:
    runs-on: ubuntu-latest
    steps:
      - name: Add SSH key to chekout a private repo
        uses: webfactory/ssh-agent@v0.5.4
        with:
          ssh-private-key: ${{ secrets.REPO_SSH_KEY }}

      - name: Checkout code
        uses: actions/checkout@v2

      # ...
      # the rest of the steps, including npm install
      # that will successfully access the private repo

```