See this forum post https://forums.ohdsi.org/t/ehr-observation-period-logic-please-take-a-look/11873 for a discussion and suggestion on defining the start and end for an observation period.
C, C1 and C2 all reference the Concept table. The aliases are needed because the concept relationship table has a reference to the source concept id and target concept id. This requires looking at two different rows in the concept table within the same query. This is accomplished by using an alias for the Concept table (C, C1, C2). The SQL statement treats each alias as if it refers to a separate copy of the Concept table.
Note in the query you reference, it should be “Maps to” with a capital “M”