I'm still trying to wrap my head around this.
So my current setup:
Cable modem (Motorola) > Router (R7000 with Circle Parental controls) > Devices (via wifi)
Now, per @michaeldavie, I should login to my modem and set DNS entry to Raspberry Pi's static IP assigned by router. And, leave everything intact in my router.
Is this a correct course of path?
I see from your screenshot above that your router is getting a public IP straight from your modem, just like @RamSet described. If that's the case, and it's not possible to put your modem into router rather than bridge mode, it won't work. If you can put the modem into router mode and connect the Pi to its LAN side, it will.
Yup, it looks like your modem can only operate as a bridge, and not as a router. Unfortunately, I don't think you'll be able to get the Circle and Pi-Hole to be able to work together in your network, unless you add another router between the modem and the R7000. It wouldn't need to be anything fancy, it would just need to support NAT and Wifi to connect the Pi Zero W.
So, that new router (R2) would have DNS and DHCP pointed to PI. Any device connected via R2 would be leveraging ad blocking capabilities while kids devices would stick with R1 WiFi and use Circle?
Ok, I disabled parental controls and pointed DNS to PI ip. Looks like it's working. However, the query log in admin panel of pi-Hole, shows router IP as client. Is this expected? Can I configure it to display actual IP?
There are two DNS settings on the router. It sounds like you've set the upstream one to point to the Pi-Hole, and that the downstream one that gets pushed to the clients over DHCP is still the router's LAN address. If you change the second one, the clients will point to the Pi-Hole directly, once they renew their DHCP lease.
Yup, it looks like the R7000 won't let you configure its DHCP server to use anything other than itself as the DNS server. In that case, you could enable DHCP on the Pi-Hole instead, as @Mcat12 suggested here: