[2022-08-12] Fun Friday Code Challenge

README.md

Code Challenge for 2022-08-12

A Window into SQL

For this code challenge, we will be diving into a bit of SQL. There are two datasets, one with programmers (id, first_name, last_name) and another with their pull requests (id, user_id, language, created_at). The challenge is to simply get the coders with the top 20 amount of pull-requests and order first by the total number of pull requests and then by language. We are only interested in results newer than '2022-07-13T00:00:00Z'.

Hint: this can be done in one SQL statement.

Bonus: Can you do this without creating a table or temp table? Bonusly points for the first to figure this out.

Good luck!

The desired output

id	prs_by_lang	first_name	last_name	language
85d3b277-9b6a-4151-954e-a37e8e0f148d	5	Billye	Hahn	c#
c47ad00c-0c43-4954-ac4d-d8f13ff98219	5	Emiko	Torphy	cobol
e849ae34-a0da-4f19-9709-d8210f023d4a	4	Blaine	Hand	java
490fb416-2fc9-4b59-be33-7a483a8088c0	4	Francene	Ankunding	ruby
ea915b3c-fd60-491a-97fa-8befc79e531e	3	Dolly	Bartell	c#
85d3b277-9b6a-4151-954e-a37e8e0f148d	3	Billye	Hahn	java
ea915b3c-fd60-491a-97fa-8befc79e531e	3	Dolly	Bartell	ruby
9683fc4e-866b-492e-8bfd-c7eb94191d13	2	Woodrow	DAmore	c#
e849ae34-a0da-4f19-9709-d8210f023d4a	2	Blaine	Hand	c#
4462e0a8-cd46-46fc-908d-3c5d1123dbb7	2	Carry	Jaskolski	cobol
9683fc4e-866b-492e-8bfd-c7eb94191d13	2	Woodrow	DAmore	cobol
490fb416-2fc9-4b59-be33-7a483a8088c0	2	Francene	Ankunding	java
8a488ec0-58dc-4804-b2a1-74d221d66391	2	Randy	Jast	java
8cf8d1fc-2178-46b6-8d4e-2158e6656742	2	Waldo	Paucek	java
9683fc4e-866b-492e-8bfd-c7eb94191d13	2	Woodrow	DAmore	java
1eedd029-c05b-4cfd-903e-04544f8539a8	2	Krystyna	Haley	perl
8cf8d1fc-2178-46b6-8d4e-2158e6656742	2	Waldo	Paucek	perl
9683fc4e-866b-492e-8bfd-c7eb94191d13	2	Woodrow	DAmore	perl
1eedd029-c05b-4cfd-903e-04544f8539a8	2	Krystyna	Haley	python
8cf8d1fc-2178-46b6-8d4e-2158e6656742	2	Waldo	Paucek	python

Data Sets

programmers

('8a488ec0-58dc-4804-b2a1-74d221d66391'::uuid,'Randy','Jast'),
('85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'Billye','Hahn'),
('4462e0a8-cd46-46fc-908d-3c5d1123dbb7'::uuid,'Carry','Jaskolski'),
('ea915b3c-fd60-491a-97fa-8befc79e531e'::uuid,'Dolly','Bartell'),
('490fb416-2fc9-4b59-be33-7a483a8088c0'::uuid,'Francene','Ankunding'),
('1eedd029-c05b-4cfd-903e-04544f8539a8'::uuid,'Krystyna','Haley'),
('e849ae34-a0da-4f19-9709-d8210f023d4a'::uuid,'Blaine','Hand'),
('8cf8d1fc-2178-46b6-8d4e-2158e6656742'::uuid,'Waldo','Paucek'),
('9683fc4e-866b-492e-8bfd-c7eb94191d13'::uuid,'Woodrow','DAmore'),
('c47ad00c-0c43-4954-ac4d-d8f13ff98219'::uuid,'Emiko','Torphy')

pull_requests

('e3e37f4f-3ab1-470f-81d8-650dc1830248'::uuid,'ea915b3c-fd60-491a-97fa-8befc79e531e'::uuid,'cobol','2022-08-09T18:32:50Z'::timestamptz),
('58d562c1-4e04-47c9-a95a-77916a6e38e7'::uuid,'490fb416-2fc9-4b59-be33-7a483a8088c0'::uuid,'typescript','2022-08-03T04:14:34Z'::timestamptz),
('b873e683-3739-4882-b7a4-6fb12e6096ef'::uuid,'c47ad00c-0c43-4954-ac4d-d8f13ff98219'::uuid,'cobol','2022-07-23T20:50:05Z'::timestamptz),
('2b49693d-a118-4807-a4a4-6ffaf0e31956'::uuid,'8cf8d1fc-2178-46b6-8d4e-2158e6656742'::uuid,'python','2022-07-21T05:43:51Z'::timestamptz),
('4285ea20-bdd4-433d-b777-ca4898ad706f'::uuid,'8cf8d1fc-2178-46b6-8d4e-2158e6656742'::uuid,'perl','2022-08-11T01:36:03Z'::timestamptz),
('d859d9d4-2f0c-4202-9aa2-93cb086163aa'::uuid,'1eedd029-c05b-4cfd-903e-04544f8539a8'::uuid,'java','2022-07-14T07:34:06Z'::timestamptz),
('49092e44-9314-42a7-b70a-076eee909d2e'::uuid,'1eedd029-c05b-4cfd-903e-04544f8539a8'::uuid,'python','2022-08-06T10:45:17Z'::timestamptz),
('1d8bbd39-82fb-483b-a8f6-d1c53ba935e5'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'cobol','2022-08-03T21:33:32Z'::timestamptz),
('b7fd3ef2-8bf3-474e-8f4e-6a06c1e2919c'::uuid,'ea915b3c-fd60-491a-97fa-8befc79e531e'::uuid,'perl','2022-07-25T03:04:39Z'::timestamptz),
('61e62813-f267-452a-8059-11e057e0b31b'::uuid,'4462e0a8-cd46-46fc-908d-3c5d1123dbb7'::uuid,'cobol','2022-08-01T14:07:23Z'::timestamptz),
('083a3308-cbff-4b36-861f-e1eaa5dae75a'::uuid,'1eedd029-c05b-4cfd-903e-04544f8539a8'::uuid,'python','2022-08-10T11:38:37Z'::timestamptz),
('4360fb07-200d-453b-ab66-75928ba70d11'::uuid,'c47ad00c-0c43-4954-ac4d-d8f13ff98219'::uuid,'java','2022-07-15T10:03:33Z'::timestamptz),
('d2264983-37d2-452f-b95a-338a799f1fa0'::uuid,'8a488ec0-58dc-4804-b2a1-74d221d66391'::uuid,'typescript','2022-07-28T05:31:44Z'::timestamptz),
('66e542f8-9d86-4da2-9d51-503987214383'::uuid,'e849ae34-a0da-4f19-9709-d8210f023d4a'::uuid,'ruby','2022-07-30T01:05:03Z'::timestamptz),
('d57dee17-6a8a-4fea-b7ed-dbd380f09f6a'::uuid,'490fb416-2fc9-4b59-be33-7a483a8088c0'::uuid,'typescript','2022-07-16T07:30:34Z'::timestamptz),
('b2687414-4e44-4d5c-a4a4-1907808fa7e5'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'c#','2022-07-24T20:00:52Z'::timestamptz),
('d218d97b-7f6e-4f2d-8d04-10b7c30b4ecc'::uuid,'1eedd029-c05b-4cfd-903e-04544f8539a8'::uuid,'typescript','2022-07-30T00:40:27Z'::timestamptz),
('c95258f5-c07d-4465-8a62-a87302dcd71c'::uuid,'8a488ec0-58dc-4804-b2a1-74d221d66391'::uuid,'ruby','2022-08-05T15:25:29Z'::timestamptz),
('c675f0c5-45bb-421f-ba0e-b7b40f1fff08'::uuid,'490fb416-2fc9-4b59-be33-7a483a8088c0'::uuid,'java','2022-07-27T21:50:43Z'::timestamptz),
('5903860d-7aac-490c-92d2-9b58abbe48ae'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'java','2022-07-17T12:53:07Z'::timestamptz),
('00890d4b-2041-428d-9481-7c71916efdbe'::uuid,'9683fc4e-866b-492e-8bfd-c7eb94191d13'::uuid,'c#','2022-08-01T15:32:00Z'::timestamptz),
('69176660-5dda-49c1-bf43-4957b89641a2'::uuid,'4462e0a8-cd46-46fc-908d-3c5d1123dbb7'::uuid,'typescript','2022-07-17T17:22:29Z'::timestamptz),
('bb771175-27d3-4107-9377-2898518e2c6f'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'perl','2022-07-19T05:11:33Z'::timestamptz),
('2c5c1f67-de3e-4f27-bcb0-acf550254214'::uuid,'1eedd029-c05b-4cfd-903e-04544f8539a8'::uuid,'ruby','2022-08-07T18:16:22Z'::timestamptz),
('8c18d92e-bbf3-4471-95d4-161fd42c36e2'::uuid,'9683fc4e-866b-492e-8bfd-c7eb94191d13'::uuid,'cobol','2022-07-18T16:55:35Z'::timestamptz),
('bb7b5782-ecc5-4f21-8a5d-1b278fc87d6f'::uuid,'8a488ec0-58dc-4804-b2a1-74d221d66391'::uuid,'python','2022-07-15T10:04:26Z'::timestamptz),
('6620cd75-3832-4bcc-803c-c748e71b9085'::uuid,'8a488ec0-58dc-4804-b2a1-74d221d66391'::uuid,'cobol','2022-07-25T15:48:17Z'::timestamptz),
('5e3deb16-8351-4ca9-8321-9e161549ffa4'::uuid,'e849ae34-a0da-4f19-9709-d8210f023d4a'::uuid,'java','2022-07-21T06:31:26Z'::timestamptz),
('cc8eb82d-ae16-44d3-a84e-5875a7313dca'::uuid,'ea915b3c-fd60-491a-97fa-8befc79e531e'::uuid,'typescript','2022-07-26T18:46:57Z'::timestamptz),
('66aa56a9-b2e7-42d9-93d5-1a18b9ef8be3'::uuid,'c47ad00c-0c43-4954-ac4d-d8f13ff98219'::uuid,'cobol','2022-08-05T05:18:58Z'::timestamptz),
('38cb9b76-051a-4ec7-a5e7-3f49457cef54'::uuid,'8cf8d1fc-2178-46b6-8d4e-2158e6656742'::uuid,'ruby','2022-07-18T03:53:39Z'::timestamptz),
('d9b8d03b-1817-424e-a86b-5977ec587415'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'c#','2022-08-01T15:38:58Z'::timestamptz),
('7a4b20c7-603e-453a-98d3-4d26ea3c5973'::uuid,'8cf8d1fc-2178-46b6-8d4e-2158e6656742'::uuid,'ruby','2022-08-04T20:16:28Z'::timestamptz),
('59c8d3a9-f968-4b62-afac-01282f31d153'::uuid,'8cf8d1fc-2178-46b6-8d4e-2158e6656742'::uuid,'java','2022-07-28T16:42:10Z'::timestamptz),
('872528de-3357-4ecd-979d-3d6cf6dddb8f'::uuid,'e849ae34-a0da-4f19-9709-d8210f023d4a'::uuid,'cobol','2022-07-24T15:34:02Z'::timestamptz),
('c2a396b5-85d9-482a-a0ac-9e3b0cb54897'::uuid,'8cf8d1fc-2178-46b6-8d4e-2158e6656742'::uuid,'c#','2022-07-18T20:14:59Z'::timestamptz),
('1fc030c9-5a42-4a44-8208-bf3ab6d2b44f'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'java','2022-07-28T22:30:46Z'::timestamptz),
('13126d55-4bc2-4020-984f-7b6d28cfd132'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'ruby','2022-07-29T17:35:12Z'::timestamptz),
('c973acd6-a517-4295-8e2e-b004be7b9899'::uuid,'e849ae34-a0da-4f19-9709-d8210f023d4a'::uuid,'perl','2022-07-30T15:34:35Z'::timestamptz),
('49ed11eb-fd58-4d77-a89f-950c4170fe4d'::uuid,'490fb416-2fc9-4b59-be33-7a483a8088c0'::uuid,'java','2022-07-23T03:43:10Z'::timestamptz),
('9770184f-b651-4ec0-b4ab-1324a6a39f16'::uuid,'8a488ec0-58dc-4804-b2a1-74d221d66391'::uuid,'typescript','2022-08-04T11:18:09Z'::timestamptz),
('45ce2a0b-b4eb-4bbd-b5f7-64fc8db5ca05'::uuid,'e849ae34-a0da-4f19-9709-d8210f023d4a'::uuid,'c#','2022-08-06T11:01:51Z'::timestamptz),
('50b4aae1-356e-4619-95e2-c62b9192bc59'::uuid,'9683fc4e-866b-492e-8bfd-c7eb94191d13'::uuid,'perl','2022-07-18T03:54:19Z'::timestamptz),
('a3e7bf46-f548-4db6-bb30-4ea63a6c8094'::uuid,'490fb416-2fc9-4b59-be33-7a483a8088c0'::uuid,'python','2022-08-09T11:16:28Z'::timestamptz),
('db0b8b8e-078f-44bc-be1d-3c7ca07fcd85'::uuid,'ea915b3c-fd60-491a-97fa-8befc79e531e'::uuid,'c#','2022-07-18T11:11:26Z'::timestamptz),
('860e8c11-1c21-4295-9755-6491e5916bdb'::uuid,'9683fc4e-866b-492e-8bfd-c7eb94191d13'::uuid,'cobol','2022-08-07T12:41:45Z'::timestamptz),
('9159e4d3-6c94-4c7f-82ec-87bb396ea43e'::uuid,'e849ae34-a0da-4f19-9709-d8210f023d4a'::uuid,'c#','2022-07-21T02:39:57Z'::timestamptz),
('a4543d72-1531-47f0-88f2-bfa87008340c'::uuid,'8a488ec0-58dc-4804-b2a1-74d221d66391'::uuid,'java','2022-07-17T02:56:13Z'::timestamptz),
('26ebc369-9c23-4825-979b-e4d0457d06ec'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'ruby','2022-08-12T10:41:53Z'::timestamptz),
('be8f8053-ec73-4a43-81ce-99a4765a701d'::uuid,'ea915b3c-fd60-491a-97fa-8befc79e531e'::uuid,'ruby','2022-08-07T02:31:15Z'::timestamptz),
('8856b149-bc62-4b00-bfd9-d34f2c40bff7'::uuid,'ea915b3c-fd60-491a-97fa-8befc79e531e'::uuid,'python','2022-08-02T18:02:45Z'::timestamptz),
('8e4b2be9-0cf2-4f69-90be-79982ee5b21d'::uuid,'c47ad00c-0c43-4954-ac4d-d8f13ff98219'::uuid,'c#','2022-08-02T02:12:59Z'::timestamptz),
('b3c9182a-78da-4a68-a31f-09507a4014ba'::uuid,'8a488ec0-58dc-4804-b2a1-74d221d66391'::uuid,'java','2022-08-06T22:38:05Z'::timestamptz),
('862d9e66-6780-4e7d-8007-742ef1bc9bf7'::uuid,'ea915b3c-fd60-491a-97fa-8befc79e531e'::uuid,'c#','2022-08-05T05:21:48Z'::timestamptz),
('d9ca0c31-c9b1-4b29-8f9a-8fcec7bf33c8'::uuid,'ea915b3c-fd60-491a-97fa-8befc79e531e'::uuid,'python','2022-07-14T17:16:54Z'::timestamptz),
('ac318ed2-2987-4572-9356-1f0c3080c4fd'::uuid,'e849ae34-a0da-4f19-9709-d8210f023d4a'::uuid,'java','2022-07-16T13:53:36Z'::timestamptz),
('9c0088e8-a1ca-48c8-9810-383636a3afec'::uuid,'c47ad00c-0c43-4954-ac4d-d8f13ff98219'::uuid,'cobol','2022-07-17T06:21:25Z'::timestamptz),
('e20471e2-4bb9-43b7-b48d-49d75bd66639'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'python','2022-08-04T07:49:29Z'::timestamptz),
('fa9eb288-6375-4c90-9574-fcfd15ab6bea'::uuid,'4462e0a8-cd46-46fc-908d-3c5d1123dbb7'::uuid,'cobol','2022-08-04T12:30:16Z'::timestamptz),
('bcebca18-f1b1-45bb-a401-948efe1ed193'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'c#','2022-07-30T22:02:07Z'::timestamptz),
('2d6a436c-ed65-4c58-94d7-e2c55a1c2f93'::uuid,'490fb416-2fc9-4b59-be33-7a483a8088c0'::uuid,'ruby','2022-07-22T02:05:52Z'::timestamptz),
('dde6e45a-e97a-4a59-a37c-c74a7b88f72c'::uuid,'c47ad00c-0c43-4954-ac4d-d8f13ff98219'::uuid,'python','2022-08-10T22:51:45Z'::timestamptz),
('1f3bc0be-90aa-49eb-b3fa-491b6f742395'::uuid,'1eedd029-c05b-4cfd-903e-04544f8539a8'::uuid,'typescript','2022-07-20T15:51:00Z'::timestamptz),
('ab638756-6eba-4f8b-9908-5acef21dbe99'::uuid,'4462e0a8-cd46-46fc-908d-3c5d1123dbb7'::uuid,'c#','2022-08-12T06:44:03Z'::timestamptz),
('9ef5a720-1484-4218-a5f7-f5d0cc0781e9'::uuid,'8cf8d1fc-2178-46b6-8d4e-2158e6656742'::uuid,'perl','2022-07-20T22:56:58Z'::timestamptz),
('dfcac88b-43ae-4c2d-9b44-48e9622c0b26'::uuid,'c47ad00c-0c43-4954-ac4d-d8f13ff98219'::uuid,'cobol','2022-08-03T03:59:15Z'::timestamptz),
('c6657f40-186c-4cb5-8200-e1a4e8562a40'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'java','2022-08-10T05:35:37Z'::timestamptz),
('a4fa5933-da0f-437c-a08d-1012252e5852'::uuid,'9683fc4e-866b-492e-8bfd-c7eb94191d13'::uuid,'java','2022-07-18T02:57:08Z'::timestamptz),
('a157ad01-b663-4a05-964f-2a708053243a'::uuid,'9683fc4e-866b-492e-8bfd-c7eb94191d13'::uuid,'typescript','2022-07-28T12:04:07Z'::timestamptz),
('4042c7b6-d60f-44c3-9b7e-c5156bae28b7'::uuid,'490fb416-2fc9-4b59-be33-7a483a8088c0'::uuid,'c#','2022-07-26T15:51:43Z'::timestamptz),
('7aaab017-d3c2-48c6-8bb6-49308567a5b5'::uuid,'4462e0a8-cd46-46fc-908d-3c5d1123dbb7'::uuid,'ruby','2022-07-28T12:43:56Z'::timestamptz),
('0bf870da-50ef-4acb-8b06-69a6bc1c6e8d'::uuid,'ea915b3c-fd60-491a-97fa-8befc79e531e'::uuid,'ruby','2022-08-05T01:22:00Z'::timestamptz),
('4779ddbd-1566-480e-ae3d-db34b8a5c85d'::uuid,'1eedd029-c05b-4cfd-903e-04544f8539a8'::uuid,'perl','2022-08-04T02:21:16Z'::timestamptz),
('66d807fb-11f1-4f3f-be0c-586b1e1a1b70'::uuid,'490fb416-2fc9-4b59-be33-7a483a8088c0'::uuid,'ruby','2022-07-18T05:57:54Z'::timestamptz),
('5233c251-a4ff-4eb0-a2ef-4ad6ca2173ce'::uuid,'e849ae34-a0da-4f19-9709-d8210f023d4a'::uuid,'java','2022-07-31T17:10:47Z'::timestamptz),
('3a1f443a-b149-4a1b-88f3-f15743601f42'::uuid,'490fb416-2fc9-4b59-be33-7a483a8088c0'::uuid,'ruby','2022-07-17T20:11:08Z'::timestamptz),
('68b40391-c8ae-4013-9950-6050472d873a'::uuid,'9683fc4e-866b-492e-8bfd-c7eb94191d13'::uuid,'perl','2022-07-22T10:41:01Z'::timestamptz),
('066c86f0-34d3-4d43-8728-e4cf87a7771e'::uuid,'8cf8d1fc-2178-46b6-8d4e-2158e6656742'::uuid,'java','2022-07-31T02:59:22Z'::timestamptz),
('e86a66b2-0c8b-42f2-92d7-370c07a1854e'::uuid,'9683fc4e-866b-492e-8bfd-c7eb94191d13'::uuid,'java','2022-08-02T16:32:26Z'::timestamptz),
('2aa49f18-713f-4487-9d78-ba73be86778a'::uuid,'490fb416-2fc9-4b59-be33-7a483a8088c0'::uuid,'ruby','2022-07-21T11:50:23Z'::timestamptz),
('9ebab036-33bb-4c9c-82cc-4b0976839b3f'::uuid,'c47ad00c-0c43-4954-ac4d-d8f13ff98219'::uuid,'perl','2022-08-11T22:48:59Z'::timestamptz),
('0bdecafc-62b7-4d12-b3ac-851d16bd48a6'::uuid,'1eedd029-c05b-4cfd-903e-04544f8539a8'::uuid,'perl','2022-07-15T03:37:09Z'::timestamptz),
('9535ba9c-0437-42cf-82b8-dca4778f5b12'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'c#','2022-07-26T13:13:37Z'::timestamptz),
('14d6bb60-c712-4e0b-a02c-ab10252577e7'::uuid,'4462e0a8-cd46-46fc-908d-3c5d1123dbb7'::uuid,'perl','2022-07-21T13:19:45Z'::timestamptz),
('62571333-ec18-4f2d-9a89-366513af68f6'::uuid,'c47ad00c-0c43-4954-ac4d-d8f13ff98219'::uuid,'cobol','2022-08-12T00:05:33Z'::timestamptz),
('2f1302e2-8e80-4b9e-a2c0-64d13088f942'::uuid,'1eedd029-c05b-4cfd-903e-04544f8539a8'::uuid,'cobol','2022-08-10T03:50:47Z'::timestamptz),
('f66dd606-aa4c-4a7c-a3ef-0660928a4985'::uuid,'8cf8d1fc-2178-46b6-8d4e-2158e6656742'::uuid,'python','2022-07-21T04:50:17Z'::timestamptz),
('b43da8fb-f651-4afe-a050-eff7e0e7e0df'::uuid,'e849ae34-a0da-4f19-9709-d8210f023d4a'::uuid,'typescript','2022-07-29T06:32:07Z'::timestamptz),
('b06c4bb2-dc2b-4ce5-9eeb-92daab31172c'::uuid,'ea915b3c-fd60-491a-97fa-8befc79e531e'::uuid,'ruby','2022-07-28T00:55:16Z'::timestamptz),
('2b778f18-2a3e-4603-befb-6969a21e3b16'::uuid,'1eedd029-c05b-4cfd-903e-04544f8539a8'::uuid,'c#','2022-07-16T08:34:48Z'::timestamptz),
('aa993493-0807-42c8-8fa0-256ca6a9899c'::uuid,'e849ae34-a0da-4f19-9709-d8210f023d4a'::uuid,'java','2022-07-28T08:34:04Z'::timestamptz),
('d3dd5cac-e296-432a-a289-b40cd80c2a23'::uuid,'ea915b3c-fd60-491a-97fa-8befc79e531e'::uuid,'typescript','2022-08-10T16:12:09Z'::timestamptz),
('96e031ab-d64e-40fc-8729-8ea026902162'::uuid,'e849ae34-a0da-4f19-9709-d8210f023d4a'::uuid,'python','2022-07-19T10:26:09Z'::timestamptz),
('f6cfb546-ea5a-45a0-80c2-148bf9b9f049'::uuid,'490fb416-2fc9-4b59-be33-7a483a8088c0'::uuid,'cobol','2022-07-21T20:41:29Z'::timestamptz),
('ba58d25e-a0c5-439d-991f-66a5836396cf'::uuid,'c47ad00c-0c43-4954-ac4d-d8f13ff98219'::uuid,'typescript','2022-07-25T21:44:51Z'::timestamptz),
('60b012e6-7c3c-4ff0-96d0-7b30e0f57d94'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'c#','2022-08-01T21:11:38Z'::timestamptz),
('648c78e2-ea3a-4765-aac5-1dcad3d44f7e'::uuid,'ea915b3c-fd60-491a-97fa-8befc79e531e'::uuid,'c#','2022-07-15T10:09:41Z'::timestamptz),
('ac2af0fb-c4d9-4335-b4a0-f2ecb14d459f'::uuid,'8a488ec0-58dc-4804-b2a1-74d221d66391'::uuid,'c#','2022-07-17T02:11:59Z'::timestamptz),
('552ac347-554d-4d84-94e7-008c95e0c40d'::uuid,'ea915b3c-fd60-491a-97fa-8befc79e531e'::uuid,'java','2022-08-07T21:54:45Z'::timestamptz),
('9a1d4e6a-1dcd-481d-a4d4-f960e2ce78d5'::uuid,'9683fc4e-866b-492e-8bfd-c7eb94191d13'::uuid,'c#','2022-07-21T07:20:17Z'::timestamptz),
('ded03e82-d429-4882-8cc0-ea64b23b589e'::uuid,'8cf8d1fc-2178-46b6-8d4e-2158e6656742'::uuid,'perl','2022-07-04T17:56:51Z'::timestamptz),
('8f9421ef-616f-4fb9-8308-a1a49fbc23bf'::uuid,'490fb416-2fc9-4b59-be33-7a483a8088c0'::uuid,'python','2022-07-07T21:39:26Z'::timestamptz),
('08259249-890c-4e7d-ba20-c764f7b3b461'::uuid,'e849ae34-a0da-4f19-9709-d8210f023d4a'::uuid,'typescript','2022-07-05T21:29:59Z'::timestamptz),
('43ed1741-660a-4950-8eec-6f89ad8104f9'::uuid,'8cf8d1fc-2178-46b6-8d4e-2158e6656742'::uuid,'java','2022-07-09T16:32:23Z'::timestamptz),
('67499bf1-f3a0-4538-b436-e0707e8fc271'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'java','2022-07-03T18:51:39Z'::timestamptz),
('f8c4aae5-0dc8-4405-bb2d-268f8d42efaa'::uuid,'9683fc4e-866b-492e-8bfd-c7eb94191d13'::uuid,'typescript','2022-07-04T00:12:04Z'::timestamptz),
('57f5bbca-e290-437f-871c-1cec4fb4d3ba'::uuid,'c47ad00c-0c43-4954-ac4d-d8f13ff98219'::uuid,'java','2022-07-09T07:47:07Z'::timestamptz),
('af3b91d7-5d12-447d-8041-e729f1dd1930'::uuid,'490fb416-2fc9-4b59-be33-7a483a8088c0'::uuid,'c#','2022-07-08T03:07:09Z'::timestamptz),
('4849d091-aff7-46e8-b97f-3518b843ed57'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'perl','2022-07-04T11:54:07Z'::timestamptz),
('c05127b4-d5ae-4f4b-8c4c-a54281009c05'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'typescript','2022-07-07T03:55:32Z'::timestamptz)

Note on dataset generation

All of this data was generated programmatically, so I promise I did not play favorites for any language. Here is how the datasets were generated

languages = %w[ruby python c# perl java cobol typescript]
users = 10.times.collect{ [ "'#{SecureRandom.uuid}'::uuid", "'#{Faker::Name.first_name}'", "'#{Faker::Name.last_name}'" ] }
# Add pull-requests within our time range
pulls = 100.times.collect { ["'#{SecureRandom.uuid}'::uuid", "'#{users.sample[0]}'::uuid", "'#{languages.sample}'", "'#{Faker::Time.between(from: 30.days.ago, to: DateTime.now).utc.iso8601}'::timestamptz"] }
# Add pull-requests outside of our time range
10.times { pulls << ["'#{SecureRandom.uuid}'::uuid", users.sample[0],  "'#{languages.sample}'", "'#{Faker::Time.between(from: 40.days.ago, to: 31.days.ago).utc.iso8601}'::timestamptz"] }
puts users.map { |row| "(#{row.join(",")})" }.join(",\n")
puts pulls.map {|row| "(#{row.join(",")})" }.join(",\n")

I'm not sure if I count as eligible for the code challenge, but here is my basic, single statement query to grab the requested data.

`
Select pgr.id, count(plrq.id) as prs_by_lang, pgr.first_name, pgr.last_name, plrq.language
from programmers pgr
, pull_requests plrq
where plrq.user_id = pgr.id
and plrq.created_at > '2022-07-13'

group by pgr.id, plrq.language
order by count(pgr.id) desc, plrq.language
limit 20

`

select p.id, count(pr.language) as prs_by_lang, p.first_name, p.last_name, pr.language
from programmers p
join pull_requests pr on p.id=pr.user_id
where pr.created_at > '2022-07-13T00:00:00Z'
group by p.id, pr.language
order by prs_by_lang desc, pr.language
limit 20;

@brakkum and @wrummery, close, but you are missing some aggregations. Those queries won't run-as-is.

There is also a way to write this without doing a "GROUP BY." There is a hint in the title of the challenge.

select
	count(*) as prs_by_lang,
	first_name,
	last_name,
	language
from
	pull_requests
join programmers on
	pull_requests.user_id = programmers.id
where
	created_at > '2022-07-13T00:00:00Z'
group by
	(language,first_name,last_name)
order by
	prs_by_lang desc
limit 20

Everyone so far has been running into the same error: SQL Error [42803]: ERROR: column "programmers.first_name" must appear in the GROUP BY clause or be used in an aggregate function

Anyone figure out the non-aggregate way yet?

Non-aggregate function: https://mode.com/sql-tutorial/sql-window-functions/

select p.id, count(pr.id) over (partition by pr.id order by pr."language") as prs_by_lang, p.first_name, p.last_name, pr."language"
from programmers p
join pull_requests pr on pr.user_id = p.id
where pr.created_at > '2022-07-13T00:00:00Z'
--group by p.first_name, p.last_name, pr."language"
limit 20

Something like that. I am not sure if you need to group by first, last, and language with the non-aggregate function or if I need to use more non-aggregates

select user_id, language, count(language) as prs_by_lang, first_name, last_name from pullrequest, programmer where created_at > '2022-07-13T00:00:00Z' and pullrequest.user_id=programmer.id group by user_id, language, first_name, last_name order by prs_by_lang desc, language limit 20;

@Austin-Mazzolini - You're sooooo close. One error with your window function aggregate though.

That works @enam-mpulse

Here was the solution I came up with when writing the challenge. This

• uses CTEs to allow querying of data without tables
• uses a Windows function to aggregate without a Group By. You don't need it in this case, but if you wanted to add a ranking by time aspect to this challenge, it would be necessary.

WITH programmers AS (
SELECT *
FROM(
VALUES 
('8a488ec0-58dc-4804-b2a1-74d221d66391'::uuid,'Randy','Jast'),
('85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'Billye','Hahn'),
('4462e0a8-cd46-46fc-908d-3c5d1123dbb7'::uuid,'Carry','Jaskolski'),
('ea915b3c-fd60-491a-97fa-8befc79e531e'::uuid,'Dolly','Bartell'),
('490fb416-2fc9-4b59-be33-7a483a8088c0'::uuid,'Francene','Ankunding'),
('1eedd029-c05b-4cfd-903e-04544f8539a8'::uuid,'Krystyna','Haley'),
('e849ae34-a0da-4f19-9709-d8210f023d4a'::uuid,'Blaine','Hand'),
('8cf8d1fc-2178-46b6-8d4e-2158e6656742'::uuid,'Waldo','Paucek'),
('9683fc4e-866b-492e-8bfd-c7eb94191d13'::uuid,'Woodrow','DAmore'),
('c47ad00c-0c43-4954-ac4d-d8f13ff98219'::uuid,'Emiko','Torphy')
	) AS t (id, first_name, last_name)
), pull_requests AS (
SELECT *
FROM (
VALUES
('e3e37f4f-3ab1-470f-81d8-650dc1830248'::uuid,'ea915b3c-fd60-491a-97fa-8befc79e531e'::uuid,'cobol','2022-08-09T18:32:50Z'::timestamptz),
('58d562c1-4e04-47c9-a95a-77916a6e38e7'::uuid,'490fb416-2fc9-4b59-be33-7a483a8088c0'::uuid,'typescript','2022-08-03T04:14:34Z'::timestamptz),
('b873e683-3739-4882-b7a4-6fb12e6096ef'::uuid,'c47ad00c-0c43-4954-ac4d-d8f13ff98219'::uuid,'cobol','2022-07-23T20:50:05Z'::timestamptz),
('2b49693d-a118-4807-a4a4-6ffaf0e31956'::uuid,'8cf8d1fc-2178-46b6-8d4e-2158e6656742'::uuid,'python','2022-07-21T05:43:51Z'::timestamptz),
('4285ea20-bdd4-433d-b777-ca4898ad706f'::uuid,'8cf8d1fc-2178-46b6-8d4e-2158e6656742'::uuid,'perl','2022-08-11T01:36:03Z'::timestamptz),
('d859d9d4-2f0c-4202-9aa2-93cb086163aa'::uuid,'1eedd029-c05b-4cfd-903e-04544f8539a8'::uuid,'java','2022-07-14T07:34:06Z'::timestamptz),
('49092e44-9314-42a7-b70a-076eee909d2e'::uuid,'1eedd029-c05b-4cfd-903e-04544f8539a8'::uuid,'python','2022-08-06T10:45:17Z'::timestamptz),
('1d8bbd39-82fb-483b-a8f6-d1c53ba935e5'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'cobol','2022-08-03T21:33:32Z'::timestamptz),
('b7fd3ef2-8bf3-474e-8f4e-6a06c1e2919c'::uuid,'ea915b3c-fd60-491a-97fa-8befc79e531e'::uuid,'perl','2022-07-25T03:04:39Z'::timestamptz),
('61e62813-f267-452a-8059-11e057e0b31b'::uuid,'4462e0a8-cd46-46fc-908d-3c5d1123dbb7'::uuid,'cobol','2022-08-01T14:07:23Z'::timestamptz),
('083a3308-cbff-4b36-861f-e1eaa5dae75a'::uuid,'1eedd029-c05b-4cfd-903e-04544f8539a8'::uuid,'python','2022-08-10T11:38:37Z'::timestamptz),
('4360fb07-200d-453b-ab66-75928ba70d11'::uuid,'c47ad00c-0c43-4954-ac4d-d8f13ff98219'::uuid,'java','2022-07-15T10:03:33Z'::timestamptz),
('d2264983-37d2-452f-b95a-338a799f1fa0'::uuid,'8a488ec0-58dc-4804-b2a1-74d221d66391'::uuid,'typescript','2022-07-28T05:31:44Z'::timestamptz),
('66e542f8-9d86-4da2-9d51-503987214383'::uuid,'e849ae34-a0da-4f19-9709-d8210f023d4a'::uuid,'ruby','2022-07-30T01:05:03Z'::timestamptz),
('d57dee17-6a8a-4fea-b7ed-dbd380f09f6a'::uuid,'490fb416-2fc9-4b59-be33-7a483a8088c0'::uuid,'typescript','2022-07-16T07:30:34Z'::timestamptz),
('b2687414-4e44-4d5c-a4a4-1907808fa7e5'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'c#','2022-07-24T20:00:52Z'::timestamptz),
('d218d97b-7f6e-4f2d-8d04-10b7c30b4ecc'::uuid,'1eedd029-c05b-4cfd-903e-04544f8539a8'::uuid,'typescript','2022-07-30T00:40:27Z'::timestamptz),
('c95258f5-c07d-4465-8a62-a87302dcd71c'::uuid,'8a488ec0-58dc-4804-b2a1-74d221d66391'::uuid,'ruby','2022-08-05T15:25:29Z'::timestamptz),
('c675f0c5-45bb-421f-ba0e-b7b40f1fff08'::uuid,'490fb416-2fc9-4b59-be33-7a483a8088c0'::uuid,'java','2022-07-27T21:50:43Z'::timestamptz),
('5903860d-7aac-490c-92d2-9b58abbe48ae'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'java','2022-07-17T12:53:07Z'::timestamptz),
('00890d4b-2041-428d-9481-7c71916efdbe'::uuid,'9683fc4e-866b-492e-8bfd-c7eb94191d13'::uuid,'c#','2022-08-01T15:32:00Z'::timestamptz),
('69176660-5dda-49c1-bf43-4957b89641a2'::uuid,'4462e0a8-cd46-46fc-908d-3c5d1123dbb7'::uuid,'typescript','2022-07-17T17:22:29Z'::timestamptz),
('bb771175-27d3-4107-9377-2898518e2c6f'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'perl','2022-07-19T05:11:33Z'::timestamptz),
('2c5c1f67-de3e-4f27-bcb0-acf550254214'::uuid,'1eedd029-c05b-4cfd-903e-04544f8539a8'::uuid,'ruby','2022-08-07T18:16:22Z'::timestamptz),
('8c18d92e-bbf3-4471-95d4-161fd42c36e2'::uuid,'9683fc4e-866b-492e-8bfd-c7eb94191d13'::uuid,'cobol','2022-07-18T16:55:35Z'::timestamptz),
('bb7b5782-ecc5-4f21-8a5d-1b278fc87d6f'::uuid,'8a488ec0-58dc-4804-b2a1-74d221d66391'::uuid,'python','2022-07-15T10:04:26Z'::timestamptz),
('6620cd75-3832-4bcc-803c-c748e71b9085'::uuid,'8a488ec0-58dc-4804-b2a1-74d221d66391'::uuid,'cobol','2022-07-25T15:48:17Z'::timestamptz),
('5e3deb16-8351-4ca9-8321-9e161549ffa4'::uuid,'e849ae34-a0da-4f19-9709-d8210f023d4a'::uuid,'java','2022-07-21T06:31:26Z'::timestamptz),
('cc8eb82d-ae16-44d3-a84e-5875a7313dca'::uuid,'ea915b3c-fd60-491a-97fa-8befc79e531e'::uuid,'typescript','2022-07-26T18:46:57Z'::timestamptz),
('66aa56a9-b2e7-42d9-93d5-1a18b9ef8be3'::uuid,'c47ad00c-0c43-4954-ac4d-d8f13ff98219'::uuid,'cobol','2022-08-05T05:18:58Z'::timestamptz),
('38cb9b76-051a-4ec7-a5e7-3f49457cef54'::uuid,'8cf8d1fc-2178-46b6-8d4e-2158e6656742'::uuid,'ruby','2022-07-18T03:53:39Z'::timestamptz),
('d9b8d03b-1817-424e-a86b-5977ec587415'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'c#','2022-08-01T15:38:58Z'::timestamptz),
('7a4b20c7-603e-453a-98d3-4d26ea3c5973'::uuid,'8cf8d1fc-2178-46b6-8d4e-2158e6656742'::uuid,'ruby','2022-08-04T20:16:28Z'::timestamptz),
('59c8d3a9-f968-4b62-afac-01282f31d153'::uuid,'8cf8d1fc-2178-46b6-8d4e-2158e6656742'::uuid,'java','2022-07-28T16:42:10Z'::timestamptz),
('872528de-3357-4ecd-979d-3d6cf6dddb8f'::uuid,'e849ae34-a0da-4f19-9709-d8210f023d4a'::uuid,'cobol','2022-07-24T15:34:02Z'::timestamptz),
('c2a396b5-85d9-482a-a0ac-9e3b0cb54897'::uuid,'8cf8d1fc-2178-46b6-8d4e-2158e6656742'::uuid,'c#','2022-07-18T20:14:59Z'::timestamptz),
('1fc030c9-5a42-4a44-8208-bf3ab6d2b44f'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'java','2022-07-28T22:30:46Z'::timestamptz),
('13126d55-4bc2-4020-984f-7b6d28cfd132'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'ruby','2022-07-29T17:35:12Z'::timestamptz),
('c973acd6-a517-4295-8e2e-b004be7b9899'::uuid,'e849ae34-a0da-4f19-9709-d8210f023d4a'::uuid,'perl','2022-07-30T15:34:35Z'::timestamptz),
('49ed11eb-fd58-4d77-a89f-950c4170fe4d'::uuid,'490fb416-2fc9-4b59-be33-7a483a8088c0'::uuid,'java','2022-07-23T03:43:10Z'::timestamptz),
('9770184f-b651-4ec0-b4ab-1324a6a39f16'::uuid,'8a488ec0-58dc-4804-b2a1-74d221d66391'::uuid,'typescript','2022-08-04T11:18:09Z'::timestamptz),
('45ce2a0b-b4eb-4bbd-b5f7-64fc8db5ca05'::uuid,'e849ae34-a0da-4f19-9709-d8210f023d4a'::uuid,'c#','2022-08-06T11:01:51Z'::timestamptz),
('50b4aae1-356e-4619-95e2-c62b9192bc59'::uuid,'9683fc4e-866b-492e-8bfd-c7eb94191d13'::uuid,'perl','2022-07-18T03:54:19Z'::timestamptz),
('a3e7bf46-f548-4db6-bb30-4ea63a6c8094'::uuid,'490fb416-2fc9-4b59-be33-7a483a8088c0'::uuid,'python','2022-08-09T11:16:28Z'::timestamptz),
('db0b8b8e-078f-44bc-be1d-3c7ca07fcd85'::uuid,'ea915b3c-fd60-491a-97fa-8befc79e531e'::uuid,'c#','2022-07-18T11:11:26Z'::timestamptz),
('860e8c11-1c21-4295-9755-6491e5916bdb'::uuid,'9683fc4e-866b-492e-8bfd-c7eb94191d13'::uuid,'cobol','2022-08-07T12:41:45Z'::timestamptz),
('9159e4d3-6c94-4c7f-82ec-87bb396ea43e'::uuid,'e849ae34-a0da-4f19-9709-d8210f023d4a'::uuid,'c#','2022-07-21T02:39:57Z'::timestamptz),
('a4543d72-1531-47f0-88f2-bfa87008340c'::uuid,'8a488ec0-58dc-4804-b2a1-74d221d66391'::uuid,'java','2022-07-17T02:56:13Z'::timestamptz),
('26ebc369-9c23-4825-979b-e4d0457d06ec'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'ruby','2022-08-12T10:41:53Z'::timestamptz),
('be8f8053-ec73-4a43-81ce-99a4765a701d'::uuid,'ea915b3c-fd60-491a-97fa-8befc79e531e'::uuid,'ruby','2022-08-07T02:31:15Z'::timestamptz),
('8856b149-bc62-4b00-bfd9-d34f2c40bff7'::uuid,'ea915b3c-fd60-491a-97fa-8befc79e531e'::uuid,'python','2022-08-02T18:02:45Z'::timestamptz),
('8e4b2be9-0cf2-4f69-90be-79982ee5b21d'::uuid,'c47ad00c-0c43-4954-ac4d-d8f13ff98219'::uuid,'c#','2022-08-02T02:12:59Z'::timestamptz),
('b3c9182a-78da-4a68-a31f-09507a4014ba'::uuid,'8a488ec0-58dc-4804-b2a1-74d221d66391'::uuid,'java','2022-08-06T22:38:05Z'::timestamptz),
('862d9e66-6780-4e7d-8007-742ef1bc9bf7'::uuid,'ea915b3c-fd60-491a-97fa-8befc79e531e'::uuid,'c#','2022-08-05T05:21:48Z'::timestamptz),
('d9ca0c31-c9b1-4b29-8f9a-8fcec7bf33c8'::uuid,'ea915b3c-fd60-491a-97fa-8befc79e531e'::uuid,'python','2022-07-14T17:16:54Z'::timestamptz),
('ac318ed2-2987-4572-9356-1f0c3080c4fd'::uuid,'e849ae34-a0da-4f19-9709-d8210f023d4a'::uuid,'java','2022-07-16T13:53:36Z'::timestamptz),
('9c0088e8-a1ca-48c8-9810-383636a3afec'::uuid,'c47ad00c-0c43-4954-ac4d-d8f13ff98219'::uuid,'cobol','2022-07-17T06:21:25Z'::timestamptz),
('e20471e2-4bb9-43b7-b48d-49d75bd66639'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'python','2022-08-04T07:49:29Z'::timestamptz),
('fa9eb288-6375-4c90-9574-fcfd15ab6bea'::uuid,'4462e0a8-cd46-46fc-908d-3c5d1123dbb7'::uuid,'cobol','2022-08-04T12:30:16Z'::timestamptz),
('bcebca18-f1b1-45bb-a401-948efe1ed193'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'c#','2022-07-30T22:02:07Z'::timestamptz),
('2d6a436c-ed65-4c58-94d7-e2c55a1c2f93'::uuid,'490fb416-2fc9-4b59-be33-7a483a8088c0'::uuid,'ruby','2022-07-22T02:05:52Z'::timestamptz),
('dde6e45a-e97a-4a59-a37c-c74a7b88f72c'::uuid,'c47ad00c-0c43-4954-ac4d-d8f13ff98219'::uuid,'python','2022-08-10T22:51:45Z'::timestamptz),
('1f3bc0be-90aa-49eb-b3fa-491b6f742395'::uuid,'1eedd029-c05b-4cfd-903e-04544f8539a8'::uuid,'typescript','2022-07-20T15:51:00Z'::timestamptz),
('ab638756-6eba-4f8b-9908-5acef21dbe99'::uuid,'4462e0a8-cd46-46fc-908d-3c5d1123dbb7'::uuid,'c#','2022-08-12T06:44:03Z'::timestamptz),
('9ef5a720-1484-4218-a5f7-f5d0cc0781e9'::uuid,'8cf8d1fc-2178-46b6-8d4e-2158e6656742'::uuid,'perl','2022-07-20T22:56:58Z'::timestamptz),
('dfcac88b-43ae-4c2d-9b44-48e9622c0b26'::uuid,'c47ad00c-0c43-4954-ac4d-d8f13ff98219'::uuid,'cobol','2022-08-03T03:59:15Z'::timestamptz),
('c6657f40-186c-4cb5-8200-e1a4e8562a40'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'java','2022-08-10T05:35:37Z'::timestamptz),
('a4fa5933-da0f-437c-a08d-1012252e5852'::uuid,'9683fc4e-866b-492e-8bfd-c7eb94191d13'::uuid,'java','2022-07-18T02:57:08Z'::timestamptz),
('a157ad01-b663-4a05-964f-2a708053243a'::uuid,'9683fc4e-866b-492e-8bfd-c7eb94191d13'::uuid,'typescript','2022-07-28T12:04:07Z'::timestamptz),
('4042c7b6-d60f-44c3-9b7e-c5156bae28b7'::uuid,'490fb416-2fc9-4b59-be33-7a483a8088c0'::uuid,'c#','2022-07-26T15:51:43Z'::timestamptz),
('7aaab017-d3c2-48c6-8bb6-49308567a5b5'::uuid,'4462e0a8-cd46-46fc-908d-3c5d1123dbb7'::uuid,'ruby','2022-07-28T12:43:56Z'::timestamptz),
('0bf870da-50ef-4acb-8b06-69a6bc1c6e8d'::uuid,'ea915b3c-fd60-491a-97fa-8befc79e531e'::uuid,'ruby','2022-08-05T01:22:00Z'::timestamptz),
('4779ddbd-1566-480e-ae3d-db34b8a5c85d'::uuid,'1eedd029-c05b-4cfd-903e-04544f8539a8'::uuid,'perl','2022-08-04T02:21:16Z'::timestamptz),
('66d807fb-11f1-4f3f-be0c-586b1e1a1b70'::uuid,'490fb416-2fc9-4b59-be33-7a483a8088c0'::uuid,'ruby','2022-07-18T05:57:54Z'::timestamptz),
('5233c251-a4ff-4eb0-a2ef-4ad6ca2173ce'::uuid,'e849ae34-a0da-4f19-9709-d8210f023d4a'::uuid,'java','2022-07-31T17:10:47Z'::timestamptz),
('3a1f443a-b149-4a1b-88f3-f15743601f42'::uuid,'490fb416-2fc9-4b59-be33-7a483a8088c0'::uuid,'ruby','2022-07-17T20:11:08Z'::timestamptz),
('68b40391-c8ae-4013-9950-6050472d873a'::uuid,'9683fc4e-866b-492e-8bfd-c7eb94191d13'::uuid,'perl','2022-07-22T10:41:01Z'::timestamptz),
('066c86f0-34d3-4d43-8728-e4cf87a7771e'::uuid,'8cf8d1fc-2178-46b6-8d4e-2158e6656742'::uuid,'java','2022-07-31T02:59:22Z'::timestamptz),
('e86a66b2-0c8b-42f2-92d7-370c07a1854e'::uuid,'9683fc4e-866b-492e-8bfd-c7eb94191d13'::uuid,'java','2022-08-02T16:32:26Z'::timestamptz),
('2aa49f18-713f-4487-9d78-ba73be86778a'::uuid,'490fb416-2fc9-4b59-be33-7a483a8088c0'::uuid,'ruby','2022-07-21T11:50:23Z'::timestamptz),
('9ebab036-33bb-4c9c-82cc-4b0976839b3f'::uuid,'c47ad00c-0c43-4954-ac4d-d8f13ff98219'::uuid,'perl','2022-08-11T22:48:59Z'::timestamptz),
('0bdecafc-62b7-4d12-b3ac-851d16bd48a6'::uuid,'1eedd029-c05b-4cfd-903e-04544f8539a8'::uuid,'perl','2022-07-15T03:37:09Z'::timestamptz),
('9535ba9c-0437-42cf-82b8-dca4778f5b12'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'c#','2022-07-26T13:13:37Z'::timestamptz),
('14d6bb60-c712-4e0b-a02c-ab10252577e7'::uuid,'4462e0a8-cd46-46fc-908d-3c5d1123dbb7'::uuid,'perl','2022-07-21T13:19:45Z'::timestamptz),
('62571333-ec18-4f2d-9a89-366513af68f6'::uuid,'c47ad00c-0c43-4954-ac4d-d8f13ff98219'::uuid,'cobol','2022-08-12T00:05:33Z'::timestamptz),
('2f1302e2-8e80-4b9e-a2c0-64d13088f942'::uuid,'1eedd029-c05b-4cfd-903e-04544f8539a8'::uuid,'cobol','2022-08-10T03:50:47Z'::timestamptz),
('f66dd606-aa4c-4a7c-a3ef-0660928a4985'::uuid,'8cf8d1fc-2178-46b6-8d4e-2158e6656742'::uuid,'python','2022-07-21T04:50:17Z'::timestamptz),
('b43da8fb-f651-4afe-a050-eff7e0e7e0df'::uuid,'e849ae34-a0da-4f19-9709-d8210f023d4a'::uuid,'typescript','2022-07-29T06:32:07Z'::timestamptz),
('b06c4bb2-dc2b-4ce5-9eeb-92daab31172c'::uuid,'ea915b3c-fd60-491a-97fa-8befc79e531e'::uuid,'ruby','2022-07-28T00:55:16Z'::timestamptz),
('2b778f18-2a3e-4603-befb-6969a21e3b16'::uuid,'1eedd029-c05b-4cfd-903e-04544f8539a8'::uuid,'c#','2022-07-16T08:34:48Z'::timestamptz),
('aa993493-0807-42c8-8fa0-256ca6a9899c'::uuid,'e849ae34-a0da-4f19-9709-d8210f023d4a'::uuid,'java','2022-07-28T08:34:04Z'::timestamptz),
('d3dd5cac-e296-432a-a289-b40cd80c2a23'::uuid,'ea915b3c-fd60-491a-97fa-8befc79e531e'::uuid,'typescript','2022-08-10T16:12:09Z'::timestamptz),
('96e031ab-d64e-40fc-8729-8ea026902162'::uuid,'e849ae34-a0da-4f19-9709-d8210f023d4a'::uuid,'python','2022-07-19T10:26:09Z'::timestamptz),
('f6cfb546-ea5a-45a0-80c2-148bf9b9f049'::uuid,'490fb416-2fc9-4b59-be33-7a483a8088c0'::uuid,'cobol','2022-07-21T20:41:29Z'::timestamptz),
('ba58d25e-a0c5-439d-991f-66a5836396cf'::uuid,'c47ad00c-0c43-4954-ac4d-d8f13ff98219'::uuid,'typescript','2022-07-25T21:44:51Z'::timestamptz),
('60b012e6-7c3c-4ff0-96d0-7b30e0f57d94'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'c#','2022-08-01T21:11:38Z'::timestamptz),
('648c78e2-ea3a-4765-aac5-1dcad3d44f7e'::uuid,'ea915b3c-fd60-491a-97fa-8befc79e531e'::uuid,'c#','2022-07-15T10:09:41Z'::timestamptz),
('ac2af0fb-c4d9-4335-b4a0-f2ecb14d459f'::uuid,'8a488ec0-58dc-4804-b2a1-74d221d66391'::uuid,'c#','2022-07-17T02:11:59Z'::timestamptz),
('552ac347-554d-4d84-94e7-008c95e0c40d'::uuid,'ea915b3c-fd60-491a-97fa-8befc79e531e'::uuid,'java','2022-08-07T21:54:45Z'::timestamptz),
('9a1d4e6a-1dcd-481d-a4d4-f960e2ce78d5'::uuid,'9683fc4e-866b-492e-8bfd-c7eb94191d13'::uuid,'c#','2022-07-21T07:20:17Z'::timestamptz),
('ded03e82-d429-4882-8cc0-ea64b23b589e'::uuid,'8cf8d1fc-2178-46b6-8d4e-2158e6656742'::uuid,'perl','2022-07-04T17:56:51Z'::timestamptz),
('8f9421ef-616f-4fb9-8308-a1a49fbc23bf'::uuid,'490fb416-2fc9-4b59-be33-7a483a8088c0'::uuid,'python','2022-07-07T21:39:26Z'::timestamptz),
('08259249-890c-4e7d-ba20-c764f7b3b461'::uuid,'e849ae34-a0da-4f19-9709-d8210f023d4a'::uuid,'typescript','2022-07-05T21:29:59Z'::timestamptz),
('43ed1741-660a-4950-8eec-6f89ad8104f9'::uuid,'8cf8d1fc-2178-46b6-8d4e-2158e6656742'::uuid,'java','2022-07-09T16:32:23Z'::timestamptz),
('67499bf1-f3a0-4538-b436-e0707e8fc271'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'java','2022-07-03T18:51:39Z'::timestamptz),
('f8c4aae5-0dc8-4405-bb2d-268f8d42efaa'::uuid,'9683fc4e-866b-492e-8bfd-c7eb94191d13'::uuid,'typescript','2022-07-04T00:12:04Z'::timestamptz),
('57f5bbca-e290-437f-871c-1cec4fb4d3ba'::uuid,'c47ad00c-0c43-4954-ac4d-d8f13ff98219'::uuid,'java','2022-07-09T07:47:07Z'::timestamptz),
('af3b91d7-5d12-447d-8041-e729f1dd1930'::uuid,'490fb416-2fc9-4b59-be33-7a483a8088c0'::uuid,'c#','2022-07-08T03:07:09Z'::timestamptz),
('4849d091-aff7-46e8-b97f-3518b843ed57'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'perl','2022-07-04T11:54:07Z'::timestamptz),
('c05127b4-d5ae-4f4b-8c4c-a54281009c05'::uuid,'85d3b277-9b6a-4151-954e-a37e8e0f148d'::uuid,'typescript','2022-07-07T03:55:32Z'::timestamptz)
) AS t (id, user_id, language, created_at)
)
select distinct
    p.id,
    count(*) over (partition by p.id, language) as prs_by_lang,
    p.first_name,
    p.last_name,
    pr.language
from programmers p, pull_requests pr
where p.id = pr.user_id
and pr.created_at > '2022-07-13 00:00:00'
order by prs_by_lang DESC, language
limit 20;

Here is my query, but haven't had time yet to try it without aggregating:

FROM programmers p
INNER JOIN pull_requests pr
ON p.id = pr.user_id
where pr.created_at > '2022-07-13T00:00:00Z'
group by pr.user_id, p.first_name, p.last_name, pr.language, p.id
order by prs_by_lang desc, language asc
limit 20;```