You cannot have the second level statistics cookies be influenced by the fourth level checkboxes: you can only have them influenced by the TODO/DONE status of the third-level headlines. In other words, you are going to have to do something like this, adding TODO states to your third-level headlines:
** Step 2 EDA (SQL) [1/2]
*** DONE Number of rides 15-16 Nov [3/3]
CLOSED: [2020-06-04 Thu 23:02]
- [X] Find number of rides for each company 15 - 16 November
- [X] Name the resulting field trips_amount and print it along with the
- [X] Sort the results by the trips_amount field in descending order.
*** TODO Yellow and Blue cabs [1/3]
- [X] Find the number of rides for every taxi company whose name contains the
words "Yellow" or "Blue" for November 1-7, 2017.
- [ ] Name the resulting variable trips_amount.
- [ ] Group the results by the company_name field.
Note that the second-level headline statistics cookie does not care about the checkboxes two levels down; it only cares about the TODO states one level down.
But having to navigate to a headline in order to change its TODO state after all of its checkboxes are checked is a pain. Fortunately, there are ways to do that automatically. In fact, I wrote some code to do that a long time ago and it's still available at the Org mode Worg site - and, miracle of miracles, I just tried it and it still works! I add it here to make the answer self-contained but you should read the linked Org Hacks entry for some caveats:
'(add-hook 'org-checkbox-statistics-hook (function ndk/checkbox-list-complete)))
(defun ndk/checkbox-list-complete ()
(when (re-search-forward "\\[\\([0-9]*%\\)\\]\\|\\[\\([0-9]*\\)/\\([0-9]*\\)\\]"
(org-todo (if (if (match-end 1)
(equal (match-string 1) "100%")
(and (> (match-end 2) (match-beginning 2))
(equal (match-string 2) (match-string 3))))
;; All done - do the state change.
One thing I should point out: in order for the hook to be run, you need to change the checkbox state either by clicking on it or with
C-c C-c from the keyboard. If you modify the state of the checkbox manually by changing the
X to a space or vice versa will not update the state of the statistics cookies until something runs the hook.Other than that, just add the code to your initialization file and you should be all set.
P.S. Thanks to @Stefan for cleaning up the code.