xgdyp commented on PR #3216:
URL:
https://github.com/apache/incubator-devlake/pull/3216#issuecomment-1260958764
Using this repo_snapshot table, we can make a query like
```
SELECT weekday(commits.committed_date) AS wd,
count(*)
FROM repo_snapshot
JOIN commits
ON commits.sha=repo_snapshot.commit_sha
WHERE repo_id LIKE 'github:GithubRepo:1:491450511'
GROUP BY wd
ORDER BY wd ;
```
to get in which day, the code has highest chance to stay in repository.
In lake, the result is Wednesday.
we can also use a query like
```
SELECT file_path,
avg(timestampdiff(day,
commits.committed_date,
now())) AS line_age
FROM repo_snapshot
JOIN commits
ON repo_snapshot.commit_sha = commits.sha
WHERE repo_id LIKE 'github:GithubRepo:1:491450511'
GROUP BY file_path
ORDER BY line_age desc;
```
to get the answer of which file has the longest average line age
and the top 10 results are :
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
